Calculating hours worked for a night shift

M

mzegers

New Member
Joined
Sep 15, 2010
Messages
1
Hello,

I am trying to create a formula to calculate hours worked (clock in/clock out). The shift that worked is from 5pm-1am or whenever the work is done.

I can calculate hours for a shift worked in the same day (8am-5pm) but not one that is worked from the night before to the next morning. Can someone help me?

Thanks :)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Try formating your cells with the Date as MM/DD/YY Time. To do this highlight cells [Format], [Number], [Date], Select the type. There are two with date and time, on standard and one military.

HTH,
 
Last edited:
Upvote 0
If you have start time in A2 and end time in B2 then this formula will give the hours worked, whether passing midnight or not

=B2-A2+(A2>B2)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Need a formula for Business Hours Mon-Fri 8AM - 5PM between two dates and times!!
Replies
3
Views
60
etaf
etaf
D
  • Solved
Timesheet Calculation
Replies
7
Views
817
jorismoerings
J
R
  • Question
Calculate Hours between timeslots
2
Replies
13
Views
180
Roy_Excel_Island_Apps
R
A
  • Question
Counting hours spent ( within certain shift hours) between two dates.
Replies
1
Views
74
eiloken
E
J
  • Question
Shift bands
2 3
Replies
29
Views
1K
felixstraube
F

Forum statistics

Threads
1,215,474
Messages
6,125,026
Members
449,204
Latest member
LKN2GO

Share this page

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