Formula to distinguish 'day shift hours' from 'night shift hours'

jana007

New Member
Joined
Aug 11, 2020
Messages
1
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hi everyone,

I'm trying to calculate the exact day shift hours and the exact night shift hours.
The day hours are from 9:00 to 18:00 and the night shift hours are from 18:00 to 9:00.
I just can't get any formula to do what I need it to do.
I would really appreciate any help on this, I'm going nuts here! :)
 

Attachments

  • book1.JPG
    book1.JPG
    49.1 KB · Views: 62

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Just to get the ball rolling. Now your example might have some typos. At least double check your numbers against mine. Now Cell Formatting is the key. Columns A & B is

1601337408908.png


Column C is

1601337460205.png




20-09-28 date 1.xlsx
ABC
1EntranceExit
206/09/2020 18:5106/10/2020 02:5308:02
306/09/2020 19:2606/10/2020 04:5309:27
404/09/2020 18:1506/09/2020 20:001465:45
506/09/2020 11:0206/09/2020 19:1008:08
605/09/2020 18:1905/09/2020 23:4105:22
705/09/2020 20:0705/09/2020 23:0502:58
805/09/2020 18:1505/09/2020 20:0001:45
905/09/2020 19:0105/09/2020 21:2002:19
1005/09/2020 15:0105/09/2020 18:2703:26
1104/09/2020 17:3404/10/2020 03:0909:35
Time
Cell Formulas
RangeFormula
C2:C11C2=B2-A2
 
Upvote 0
I suggest to apply sbTimeDiff once for day shift hours (9:00 - 18:00) and twice for night shift hours (0:00 - 9:00, 18:00 - 24:00):


Another approach might be to subtract 9 hours from both start date time and end date time and count the hours < 9:00 vs > 9:00 per day. A bit error prone and maintenance unfriendly...
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top