Time formula for late work hours

Hi,

Im new here, but I need to ask about two things concerning the time function.

As you can see on the picture, I have an opening hour and a closing hour scheduled.
To quickly explain the terms. Timer means hours, and kveld/helg means evening/weekend.

timeschedule.png


Question is:

1) Why is the first time showing 3,0 and not 3,25?
2) Whats the obvious formula to actually count in hours that is evening (after 17:00)? The hours written now is just pushed in manually.
3) Whats the simplest formula to count hours between two times?


Please help.

Hi and welcome.

First thing. to learn here.. upload example files, not pictures.. Debugging pictures is often very difficult.

to answer your questions based on picture only.

1) because using TIME function is not needed, worse it causes the error. simply use E4-D4 to find the hours between "fra"and "Til"
2) formula could be

for time notation

Formula:

Please Login or Register  to view this content.

or for decimal value

Formula:

Please Login or Register  to view this content.

3) see 1:, but if you want it to show as decimal value 3,25 then multiply by 24 =(E4-D4)*24

if you also have to (til) times after midnight then you need a slightly more complicated formula

Formula:

Please Login or Register  to view this content.

where the (E4<D4) will add 1 to E4 to be bigger then D4.
Times in excel are fractions of 1
so 6:00 equals 0,25 and 18:00 equals 0,75
when the end time is smaller then the begin time then you get a negative value and times cannot be negative therefore you need to make the endtime bigger (by adding 1) to get a correct time worked.

Works perfectly, thank you!

Another question while were at it. What if I have someone starting after 17, lets say 19 and ends at 23? With this formula I get 6 h since Im counting from 17. Correct answer would be 4 obviously. Since hes working 4 h. and all at night.

Why are you counting from 17?