Excel Calculating Time

[ziad alsayed]

dear all


i need to calulate the overtime between Two Times , the closing time is
<TABLE style="WIDTH: 61pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=81><COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 61pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20 width=81 align=right>5:30:00 PM</TD></TR></TBODY></TABLE>
let assune a staff clocked on 7:30:00 PM , how can i get the number 2 which is the Overtime.

the same is the staff Clocked 6:00:00 PM, i should get 0.5

appreciate any help

 

[AlphaFrog]

Closing time is A1
Staff clocked time is B1

=If(B1>A1, (B1-A1)*24, "No Overtime")

 

[barry houdini]

If clock time is in A2 then try

=MAX(0,A2*24-17.5)

 

[ziad alsayed]

thanks alphaFrog and Barry Houdini, both Works.

Aoppreciate if you can explain it to me.

i mean with alphaFrog formula why it is *24
and with barry Houdini why is the clock time*24-17.5

appreciate your help.

 

[AlphaFrog]

*24 will convert serial time into hours.

How Excel Stores Dates And Times

 

[ziad alsayed]

thanks alpha , it is now clear.

i am really grateful for the link.