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

 

[erik.van.geit]

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

INTRESTING INFO FOR "decimal=COMMA-users"
now it's working for me too, THANKS !!
found the problem
it's a translation issue
first I had changed the "." in ","
but didn't notice the "0.00" which had to be changed "0,00"

 

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

Thanks Erik,

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

also works thank you.

All the Best.
SHADO

 

[barry houdini]

Actually this is probably simpler and doesn't give you headaches between , and .

=MOD(TEXT(B1*100,"0\:00")-TEXT(A1*100,"0\:00"),1)*24

 

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

You guys are awesome.

Thanks again.
All the Best.
SHADO

 

[erik.van.geit]

Actually this is probably simpler and doesn't give you headaches between , and .

=MOD(TEXT(B1*100,"0\:00")-TEXT(A1*100,"0\:00"),1)*24

YES !
this turns out very intresting
where did you find this syntax, Barry ?
"0\:00"
I didn't see a backslash in the customformats

 

[barry houdini]

Hi Erik,

From the help files on custom number formats:

To display both text and numbers in a cell, enclose the text characters in double quotation marks (" ") or precede a single character with a backslash (\)

so I'm using the backslash to designate the : as text, using the quotes it would look like

=MOD(TEXT(B1*100,"0"":""00")-TEXT(A1*100,"0"":""00"),1)*24

...which isn't so pretty.....

 

[erik.van.geit]

thank you, Barry
always telling to everybody "look in the helpfiles"
this time didn't do it myself , just thinking it would be available in the customlist