Time Format

[S.H.A.D.O.]

Hi everyone,

I would like to be able to enter a start time for example as 6.15 ( with a dot as opposed to a : ) and a finish time as 14.45. I would then like to calculate the total number of hours worked please ( the previous would equate to 8.50 hours ).

Any help will be greatly appreciated.
All the Best.
SHADO

 

[barry houdini]

If start time is in A1 and end time in B1 try

=MOD(SUBSTITUTE(B1,".",":")-SUBSTITUTE(A1,".",":"),1)*24

format as number

 

[erik.van.geit]

Hi,

8:50
or
8:30 ?

kind regards,
Erik

 

[S.H.A.D.O.]

Barry,

The only thing is that if I input the start time as 5.00 and the finish time as 11.00, the formula returns a zero hours worked.

Erik,

The 8.50 equates to 8 and a half hours worked.

Thank you both for the replies.
All the Best.
SHADO

 

[barry houdini]

Barry,
The only thing is that if I input the start time as 5.00 and the finish time as 11.00, the formula returns a zero hours worked.

OK then try

=MOD(SUBSTITUTE(TEXT(B1,"0.00"),".",":")-SUBSTITUTE(TEXT(A1,"0.00"),".",":"),1)*24

 

[erik.van.geit]

Hi,

I cannot get Barries trick to work, will try again.

This works for me, but the longer looking at it, the more I wouldn't advise such a counterintuitive layout.
  A     B     C       
1 start stop  elapsed 
2 6,15  14,45 8,5     
3 5     11    6       
4 9     21,3  12,5    

Calculation
[Table-It] version 05 by Erik Van Geit

RANGE FORMULA (1st cell)
C2:C4 =TRUNC(B2)-TRUNC(A2)+ROUND((B2-TRUNC(B2)-A2+TRUNC(A2))*100,0)/60

[Table-It] version 05 by Erik Van Geit

not talking of the problems when starting at 23.00 and ending at 4.00
kind regards,
Erik

 

[barry houdini]

Another way would be

=MOD(DOLLARDE(B1,60)-DOLLARDE(A1,60),24)

.....although DOLLARRDE requires Analysis ToolPak

 

[barry houdini]

not talking of the problems when starting at 23.00 and ending at 4.00

The formulas I posted will all take care of shifts which pass from one day to the next

 

[erik.van.geit]

not talking of the problems when starting at 23.00 and ending at 4.00

quickly fixed that one

RANGE FORMULA (1st cell)
C2:C4 =TRUNC(B2)-TRUNC(A2)+ROUND((B2-TRUNC(B2)-A2+TRUNC(A2))*100,0)/60+(A2>B2)*24

[Table-It] version 05 by Erik Van Geit

(perhaps missing some obvious change to make it shorter)

 

[S.H.A.D.O.]

Hi Barry,

OK then try :-

=MOD(SUBSTITUTE(TEXT(B1,"0.00"),".",":")-SUBSTITUTE(TEXT(A1,"0.00"),".",":"),1)*24

works Perfectly, thank you. It is exactly what I was after.

Thank you Barry & Erik for your time & effort on this.
Have a good evening.

All the Best.
SHADO