calculating shift times work out hours worked

RADICAL_THOUGHT

New Member
Joined
Aug 30, 2024
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hello, I am trying to calculate between 2 shift times (Start - left cell, Finish - right cell), so the circled area has a total in this case the number 2. I would also like it to tally in the total box at the bottom of that line. i've also added a key for shift times.
 

Attachments

  • Screenshot 2024-09-15 131538.png
    Screenshot 2024-09-15 131538.png
    42.2 KB · Views: 9

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
You will need to add AM/PM to your times so that this function can be used but is this what you're looking for? Column C cells are formatted using Custom format:
h" Hours":mm" Mins"
Book1
ABC
1Time InTime OutHours Worked
28:00 AM10:45 AM2 Hours:45 Mins
37:30 AM2:00 PM6 Hours:30 Mins
Sheet1
Cell Formulas
RangeFormula
C2:C3C2=B2-A2
 
Upvote 0
Why should it be 2? Is that the number of hours worked according to start and finish? If so, do you want a time value, or a decimal number of hours? Here is a solution for both. For the total at the bottom, just use SUM.

$scratch.xlsm
ABCD
1Time ValueDecimal Hours
205:0007:0002:002.0
Time Sheet 2
Cell Formulas
RangeFormula
C2C2=B2-A2
D2D2=ROUND((B2-A2)*24,1)


If you are just calculating hours, why do we need a key for shift times? I'm concerned we're not getting your whole question here.
 
Upvote 0
The AM/PM is just display formatting. The underlying value is still a time value, so a formula will work the same way regardless.
Correct, but depending on start and finish times the formula may need to be changed because you must always subtract the smaller from the larger. In your example what would you get if person clocked in at 7:00 AM and clocked out at 5:00 PM?
 
Upvote 0
=SUM(XLOOKUP(C7:D7,{"E";"F";"G";"I";"J";"K";"L";"M";"N";"P";"Q";"R";"S";"T";"U";"V"},{5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},0,0)) so I was using this to calculate conditional formatted formulas (which is why added the key) but it adds the times together. what I want is say 09:00 - 2100 = 12 or 13:00 - 18:00 = 5 I need the same thing but with it accepting say F - E = 1 . so without any decimal places. I'm quite new so using formulas I've been given from other projects.

thankyou for all help provided
 
Last edited:
Upvote 0
Correct, but depending on start and finish times the formula may need to be changed because you must always subtract the smaller from the larger. In your example what would you get if person clocked in at 7:00 AM and clocked out at 5:00 PM?
Then the time would be 07:00 and 17:00 and it would work fine.
 
Upvote 0
what I want is say 09:00 - 2100 = 12
So your start and finish times can cross midnight? That needs a different formula.

$scratch.xlsm
ABCD
1Time ValueDecimal Hours
205:0007:0002:002.0
321:0009:0012:0012.0
422:0009:0011:0011.0
513:0009:0020:0020.0
Time Sheet 2
Cell Formulas
RangeFormula
C2:C5C2=MOD(B2-A2+1,1)
D2:D5D2=ROUND(MOD(B2-A2+1,1)*24,1)
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

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