functions

anu22 · Newbie Joined: 19 Apr 2009 Posts: 3 Location: Estonia

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 · 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

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

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)