Calculation of duration in hh:mm during pre-defined time range

[infratunes]

Dear all,

I would like to calculate the time recorded during 2 particular ranges:

• Saturday (Time recorded between 00:01 and 11:59)
  and
• Saturday (Time recorded between 12:00 and 23:59)

Example:

A person starts her shift at 10am and finishes at 6pm

The table should display:

• 2 hours in the column Saturday (Time recorded between 00:01 and 11:59)
  and
• 4 hours in the column Saturday (Time recorded between 12:00 and 23:59)

I have done some trials as you could see in the file but I did not manage to achieve what I wanted.

Thanks for your help!

[sandy666]

Change your time to proper time not text

Time format

	B	C
1	Clockin	Clokout
2	9:53	19:09
3	06:47	17:49
4	OFF	OFF
5	06:56	17:21
6	10:25	23:22
7	10:28	21:06
8	06:58	17:00
9	OFF	OFF
10	OFF	OFF
11	06:55	17:53
12	14:23	00:25
13	14:35	22:17
14	14:52	20:23
15	07:51	18:16
16	14:26	23:15
17	10:00	19:02

General format

	B	C
1	Clockin	Clokout
2	0.411805556	0.797916667
3	0.282638889	0.742361111
4	OFF	OFF
5	0.288888889	0.722916667
6	0.434027778	0.973611111
7	0.436111111	0.879166667
8	0.290277778	0.708333333
9	OFF	OFF
10	OFF	OFF
11	0.288194444	0.745138889
12	0.599305556	0.017361111
13	0.607638889	0.928472222
14	0.619444444	0.849305556
15	0.327083333	0.761111111
16	0.601388889	0.96875
17	0.416666667	0.793055556

[infratunes]

Thank you, I have updated the file with the correct cell format but my question still remains valid.

[Roel Jongman]

I've created formulas for all colomns except sundays..

when there are time tresholds it is easier to test them in min or max formulas combined with if

one of my comments was also all times needed to be true times but I see that is already solved..

[infratunes]

Thank you very much Roel! That"s perfect

[sandy666]

---

If that takes care of your original question, & to say Thanks and for better Motivation, please

1. click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
   then
2. select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.

If you did it already - ignore it.
Thank you.

[infratunes]

YEs I have added reputation already

One more question before I close the thread, what would be the formula for worked hours between 6:00am and 8:00am ?

Kind regards,

[Roel Jongman]

I have added a column at the end for that requirement.

Also I saw later column D and E have header Weekday. does this mean they do not need to calculate if weekday is Saterday or Sunday.
Just in case this is so I build in this version a variation of the earlier formula that will exclude sat and sun from the calculation of time in col D and E.

see attachment.

[infratunes]

Dear Roel,

Thanks again very much for your kind help.

In the column I3 of your file, the result should be 1:13 instead of 0:47 (The person has worked 1:13h between 6 and 8 am). I tried to change the formula but I could not manage to make it work.

Sorry for the lack of clarity in my original request.

Kind regards,

[Roel Jongman]

Your request was clear, I just confused myself my first try I had 1:13 and then decided to change is..
Calculating with time and time windows is often a bid of a puzzle.

At least you now have most all possibilities in one sheet for future reference