How to calculate night hours

[Elvers]

Hi all. Apologies but I am a novice requiring some advice. I have a time sheet which with a start and end time. I can add the times up to give me a running total but I want to have night hours worked total and can't work out how to do it. Night hours are from 11pm to 6am. Hope my question makes sense, and any advice appreciated.

[Roel Jongman]

your question makes a lot of sense from experience.. many people struggle with this.. Times are a fraction of 1 so 11pm = 0,958333 6am = 0,25
and Excel cannot calculate with negative time. there are several ways to go around the problem but the basic Idea is to make the endtime greater then the startvalue.

If starttime is in a1 and endtime is in B1 then I like to use

Please Login or Register  to view this content.

The --(A1>B1) is a trick to convert a True or false statement to a 0 or 1.
So if The starttime (11pm) is greater then end time(6am) it the statment is true or value 1 the sum then becomes 1+0,25-0,958333 = 0,2966667 or 7:00 hours



Another often used formula is

Please Login or Register  to view this content.

which comes down to exactly the same as the first solution but might be easier to read / understand what is happening.

[6StringJazzer]

Hello,

I wrote a VBA function for this: sbCountHours.

But: I am not allowed to present a link here.

Regards,
Bernd P

Feel free to post your code here, using code tags. What you cannot do is promote your own web site.