[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
anu22
Newbie

Joined: 19 Apr 2009
Posts: 3
Location: Estonia

 Posted: Sun Apr 19, 2009 3:34 am    Post subject: functions I've a big problem with my table. How can I calculate different values using different formulas and formating? Data I have: workdates 01.01.2009-31.01.2009, day beings at 8am and ends at 16pm. On Fridays, lunch is 30 minutes, Monday-Thursday 1 hour. Sun and Sat is free days. I need some kind on function to put lunch time into the table, it isn not allowed to just type it. And i need to calculate workhours every day, i can be done with HOUR function? I hope You can help me.
keme
Moderator

Joined: 30 Aug 2004
Posts: 2732
Location: Egersund, Norway

 Posted: Sun Apr 19, 2009 4:17 am    Post subject: Step by step: I assume your dates are in column A, starting from row 2 (reserving row 1 for headings), and including saturday and sunday dates. I'd use column B for a numerical weekday designator: B2: =WEEKDAY(A2;0) Column C, starting time: C2: =IF(B2>4;0;TIME(8;0;0)) Column D, finish time: D2: =IF(B2>4;0;TIME(16;0;0)) Column E, duration of lunch: E2: =TIME(0;30;0)*(1+SIGN(4-B2)) Then the total in column F: F2: =D2-C2-E2 Copy the range B2:F2 down as far as you need.
anu22
Newbie

Joined: 19 Apr 2009
Posts: 3
Location: Estonia

 Posted: Sun Apr 19, 2009 5:31 am    Post subject: Thank You! I was very helpful. But now I have a new problem. I have to calculate how much overwork I did in one month. But some overwork hours are with "minus", because they vere done in Sat or Sun. How can I calculate monthlt overwork with those minuses?
anu22
Newbie

Joined: 19 Apr 2009
Posts: 3
Location: Estonia

 Posted: Sun Apr 19, 2009 9:44 am    Post subject: Basicly I have problem with the formula making IF function. In one function must be different requirements: B3<5 and F3>"00:00" then needed cell valeue is F3-"08:00" B3>4 and F3>"00:00" then needed cell value is F3 Those two lines should be in one formula. I try to explane a bit. >5 and >4 are weekdays markers (0-MON, 6-Sun) 00:00 means that no work hours were done F3 is workhours that are done in one day. And i have to firn overwork hours, that is why I -08.00 (8hours are normal workday)
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum