Calculating Time Span Formula Problem

[cmccambridge]

I have the following formula to calculate a span of time ie F122 has 9:00PM E122 has 3:00 PM

=IF(E122=0,"",(F122-E122)*24)

Does anyone know how to change it so that if someone works past 12AM it calculates correctly

ie 6PM-1AM

Thanks

 

[CodeNinja]

You have 2 options: First, enter them as dates with time. Then you can just use your normal calcuation. Otherwise, you could use the following formula, but it only works for periods less than 24 hours. If someone worked for 28 hours, it would show only 4 hours worked...

=IF(ISBLANK(E12),"",IF(F12<E12,24+(F12-E12)*24,(F12-E12)*24))

 

[Scott Huish]

=if(count(e22:f22)=2,mod(f22-e22,1)*24,"")

 

[cmccambridge]

its coming up as a 0

 

[cmccambridge]

Nevermind! input error, works great! Thanks

 

[CodeNinja]

Hrm... my formula got lost...

=IF(ISBLANK(E12),"",IF(F12 < E12,24+(F12-E12)*24,(F12-E12)*24))<e12,24+(f12-e12)*24,(f12-e12)*24))[ code]<="" html=""></e12,24+(f12-e12)*24,(f12-e12)*24))[>