TimeSheet! Working PM to AM

K

Kjaltrik

I'm making a timesheet and it's working good.
But I can't work out how to calculate mu working time from ex: 15:00 to
18:30 lunchbreak and then 19:20 to 02:00. The calculated anser I get is
"-13.83"

I hope somebody can help me

Thanx
 
B

Bob Phillips

Try this for a time result

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
S

Sandy Mann

Kjaltrik,

With the first two times in C3 & D3 and the secong two in E3 & F3 try:

=D3-C3+MOD(F3-E3,1)

or

=D3-C3+F3-E3+(E3>F3)

and format as time.

If you are adding up several day's times custom format the SUM() cell as
"[h]:mm" (without the brackets)


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
B

Bob Phillips

Sorry I meant

=(B1-A1)+MOD(D1-C1,1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
S

Sandy Mann

mama no teeth said:
E1 =(B1-A1)+(D1-C1+1)
Click to expand...

A word of caution. Whilst this works for the times given, if the last time
was in the same day as the other times, for example at 23:00, then it would
be adding one day to the time, giving an actual result of 31:10 masked by
the formatting to show as only 7:10. (Reformat the cell as [h]:mm to see).
5 days would add up to 155.50 hours (formatted as [h]:mm), instead of the
correct result result 35:50.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
Top