Calculating day shift and night shift hours from date/time

[BenR]

I want to find the hours worked in day shift and night shift. Day shift is from 06:30 to 18:30 and night shift is from 18:30 to 06:30. Listed below is an example of my date/times.
Note that the night shift carries over to the next day.

Start/Finish
21/12/09 07:00 to 21/12/09 11:09
21/12/09 07:46 to 21/12/09 14:41
21/12/09 12:13 to 21/12/09 22:08
21/12/09 16:40 to 21/12/09 18:05
21/12/09 19:40 to 22/12/09 02:34
21/12/09 23:20 to 22/12/09 04:39
22/12/09 02:06 to 22/12/09 06:15

any ideas?

 

[Bazut]

Hello,
can someone help me with this calcolations, I see it is similar to Bill's table
Here is an example:

[TABLE="width: 387"]
<tbody>[TR]
[TD]Hourly Rates:[TABLE="width: 200"]
<tbody>[TR]
[TD][TABLE="width: 448"]
<tbody>[TR]
[TD="colspan: 4"]Day Shift[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD]Nigt Shift[/TD]
[TD]$12[/TD]
[/TR]
[TR]
[TD]Day Shift Weekend[/TD]
[TD]$13[/TD]
[/TR]
[TR]
[TD]Nigt Shift Weekend[/TD]
[TD]$14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 448"]
<tbody>[TR]
[TD="colspan: 4"]Day Shift from 6:00 AM to 8:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nigt Shift from 8:00PM to 6:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day Shift Weekend 6:00 AM Saturday to 8:00 PM Sunday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nigt Shift Weekend from 8:00PM Friday to 6:00 AM Monday

Week 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Log in[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Log Out[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl65, width: 82"]Night Hours[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl65, width: 82"]Day Hours[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Total Pay[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]Monday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9:00 PM[/TD]
[TD]7:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]Tuesday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4:00 PM[/TD]
[TD]1:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]Wednesday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3:00 PM[/TD]
[TD]00:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]Thursday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]11:00 PM[/TD]
[TD]8:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]Friday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]Saturday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]Sunday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]TOTAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Week 2
Week 3
Week 4
TOTAL

Regards

 

[Michael Avidan]

Please attach your workbook, with all the data inside, to your next post AND/OR upload it to any file hosting site and rturn to post the direct link to the file.

Michael Avidan
MS Excel MP

 

[Bazut]

Please attach your workbook, with all the data inside, to your next post AND/OR upload it to any file hosting site and rturn to post the direct link to the file.

Michael Avidan
MS Excel MP

Hi Michael,

This is the file: http://dox.bg/files/dw?a=b248cd6e2d

Thanx

 

[Michael Avidan]

Hi Michael,

This is the file: DOX.bg -

Thanx

I'm sorry but:

1)I can't use this interface. It doesn't let me copy/paste, it cuts-out some characters of what I type, etc.

2) Try to implement my formula to find the Night shifts hours.

3) Do not use Days names because they will mess your IF checking. Insead use real Dates and in an adjacent column convert them into day names - either with the WEEKDAY Function or with custom Format: dddd

Sorry...

Micahel

 

[Bazut]

I'm sorry but:

1)I can't use this interface. It doesn't let me copy/paste, it cuts-out some characters of what I type, etc.

2) Try to implement my formula to find the Night shifts hours.

3) Do not use Days names because they will mess your IF checking. Insead use real Dates and in an adjacent column convert them into day names - either with the WEEKDAY Function or with custom Format: dddd

Sorry...

Micahel

Thanx Michael,

The interface doesn't matter, it is just to understand what is the idea. I want every month just to enter the shifts and get all calculations, the hardest thing is to find
Night and Day hours for every shift because of this rules:

[TABLE="width: 384"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD="colspan: 4"]Day Shift from 6:00 AM to 8:00 PM[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Nigt Shift from 8:00PM to 6:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Day Shift weekend 6:00 AM Saturday to 8:00 PM Sunday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Nigt Shift weekend from 8:00PM Friday to 6:00 AM Monday

Ivaylo

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[soberskin]

Hi, I know that this is an old post, but I needed this also. I got round it by using the mid function on a date field.

=MID(A1,6,20)

Format this cell as a number and this gives you a numeric value. You then have a value that can be used to identify if a date is day shift or night shift.

Hope this helps someone. Cheers.

 

[Dalboz]

http://jpg.co.il/download/53bbac4b99bb7.png

Katia,

1) My english is much worse than your therefore it will be difficult for me to explain the "complicated formula.

2) In the above link you will find the same formula reffering to worksheet cells

Michael Avidan
MS Excel MVP (2009-2015)
ISRAEL

Michael, could you please give some hints to achieve a second restriction on the day shift.
E.g Day Shifts: #1 From 5:30 AM to 7:30 AM and #2 From 4:30 PM to 7:30 PM
Night Shift From 7:30 PM to 5:30 AM

I just don't understand how to fallback between those two ranges of dates

 

[me1942]

Thank you Micky, that was really helpful, thank you again you saved my hours and me as well.

 

[abrenesv]

Bill,

I went to the UDF you published on late 2009, this works perfect when inbetween start and end time is < 24 hrs. Little challenge for you, how that would be if difference between start and end time is > 24hrs? let's say 2 days, 4 days... 7 days actually, 14 shifts. I am trying to use it to assign quantity of down time hours to 4 shifts during the week; shifta, shiftb, shiftc and shiftd --- > from 6:00hrs to 18:00hrs shifta and shiftc (swinging on every other wed day shift) and from 18:00hrs to 6:00hrs shiftb and shiftd (swinging on every other sat night shift) In a week, day shifts swing, next week night shifts swing.

As I expressed before the VFA function code works like a charm for 2 shifts.... I would love to see that for more than two shift, is there a way to do the function as a sequence that loops itself after 3rd shift?

thank you,

Altra Volta from the tropics.

 

[JimmyBoats]

hi,

The formula solution is no longer available does anyone still have it?