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: 15

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
148
Office Version
  1. 2019
Platform
  1. Windows
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
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
154
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,130,060
Messages
5,639,828
Members
417,117
Latest member
Ravi Pandey

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
Top