time that crosses mid night

[lezawang]

Hi
Someone worked from 10:00pm to 6:00am next day. I want to calculate the difference. So I tried

A1=10 pm
B1= 6 am

C1=(A1-B1)*24

But did not work

I watched a video and the guy said use =Mod((B1-A1),1)

But did not explain why. I know what mod is used for but how this idea came from? Mod is used for totally something else.

Another question please:

1 am in excel is basically 1/24

what do you call 1/24? and what do you call (1/24)*24

Thank you very much

 

[Dave Patton]

Evaluate the formulas; you can use Excel's Formula Evaluate

N.B. 1 day is 24 hours
12 hours is .5 of a day
8 hours is 1/3 ie .3333 of a day

Excel 2010
	A	B	C	D
1	22:00	6:00	8
2	22:00	6:00	8
3
4	1/24	fraction of a day 1/24 or 1 hour
5	1	one hour
6
3d

Cell Formulas
Range	Formula
C1	=MOD(B1-A1,1)*24
C2	=(B2-A2+(A2>B2))*24
A5	=1/24*24

 

[lezawang]

Thank you for your help and reply. Let me explain to you what I understood and please correct me if I am wrong.

Mod() will return the number if the number is less than 1. Not only returns the number but change the sign to + if the number is negative

Mod(0.5,1) = 0.5
Mod(-0.5,1) = 0.5

So using Mod is basically to get rid of the negative when I subtract AM time from PM time crossing midnight. Am I right?

 

[Dave Patton]

Try Formula Evaluate on the two examples that I posted.