computing night working time

**fatbird** · 05-01-2010, 08:00 AM

Hello
I need to calculate night working time.
I must have a column to register every day "start time" and another one to register “end of time".
I need to get automatically:
- "worked time" - for this one I don't need help.
- "night time" - this is the problem.
The night time is between 20:00 and 06:00 (next day).
Is it possible to do this without macros?
Thank you for your help.

**royUK** · 05-01-2010, 08:14 AM

This might help,the site also has a lot of information on calculating times

**fatbird** · 05-01-2010, 09:30 AM

Originally Posted by royUK

This might help,the site also has a lot of information on calculating times

Thank you very much.
It`s an interesting and useful web which is already in my bookmarks. Anyway it could not solve my problem.

**daddylonglegs** · 05-01-2010, 11:18 AM

Hello fatbird,

Assuming you have a start time in A2 and end time in B2 then this formula in C2 will give the total hours worked

=MOD(B2-A2,1)

then in D2 use this formula to calculate all hours between 20:00 and 06:00

=C2-(A2>B2)*MEDIAN(0,B2-1/4,7/12)-MAX(0,MIN(5/6,B2+(A2>B2))-MAX(1/4,A2))

format all cells as time

**fatbird** · 05-01-2010, 04:52 PM

Originally Posted by daddylonglegs

Hello fatbird,

Assuming you have a start time in A2 and end time in B2 then this formula in C2 will give the total hours worked

=MOD(B2-A2,1)

then in D2 use this formula to calculate all hours between 20:00 and 06:00

=C2-(A2>B2)*MEDIAN(0,B2-1/4,7/12)-MAX(0,MIN(5/6,B2+(A2>B2))-MAX(1/4,A2))

format all cells as time

No way!

I applied both formulas, changing "," by ";" (Excel 2007).
I tried several time formats and I just got "#N/A" in C2 and D2.
Are you sure about using "MOD" in tht first one?

Thank you anyway, daddylonglegs.

**daddylonglegs** · 05-01-2010, 05:03 PM

Originally Posted by fatbird

Are you sure about using "MOD" in tht first one?

Yes, it's not the only way....but it's one way to do it....see the attached example

**fatbird** · 05-01-2010, 05:14 PM

Originally Posted by daddylonglegs

Yes, it's not the only way....but it's one way to do it....see the attached example

Outstanding!
Thank you very much, daddylonglegs.
You have been very helpful and nice. That's exactly what I need.

**royUK** · 05-02-2010, 03:26 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save