calculating shift times work out hours worked

[RADICAL_THOUGHT]

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.

 

[Skyybot]

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
	A	B	C
1	Time In	Time Out	Hours Worked
2	8:00 AM	10:45 AM	2 Hours:45 Mins
3	7:30 AM	2:00 PM	6 Hours:30 Mins
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=B2-A2

 

[6StringJazzer]

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
	A	B	C	D
1			Time Value	Decimal Hours
2	05:00	07:00	02:00	2.0
Time Sheet 2

Cell Formulas
Range	Formula
C2	C2	=B2-A2
D2	D2	=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.

 

[6StringJazzer]

You will need to add AM/PM to your times

The AM/PM is just display formatting. The underlying value is still a time value, so a formula will work the same way regardless.

 

[Skyybot]

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?

 

[RADICAL_THOUGHT]

=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

 

[6StringJazzer]

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.

 

[6StringJazzer]

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
	A	B	C	D
1			Time Value	Decimal Hours
2	05:00	07:00	02:00	2.0
3	21:00	09:00	12:00	12.0
4	22:00	09:00	11:00	11.0
5	13:00	09:00	20:00	20.0
Time Sheet 2

Cell Formulas
Range	Formula
C2:C5	C2	=MOD(B2-A2+1,1)
D2:D5	D2	=ROUND(MOD(B2-A2+1,1)*24,1)