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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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

D
  • Solved
Timesheet Calculation
Replies
7
Views
752
jorismoerings
J
J
  • Question
Shift bands
2 3
Replies
29
Views
972
felixstraube
F
F
  • Question
Calculation when 3 conditions are met
Replies
8
Views
392
Fidbeck
F
T
  • Solved
Finding the working hours between two dates and times
2
Replies
12
Views
283
kvsrinivasamurthy
K
S
  • Question
Automatic accrual calculations by day with accrual rate?
Replies
0
Views
222
Sunshine8790
S

Forum statistics

Threads
1,214,599
Messages
6,120,448
Members
448,966
Latest member
DannyC96

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