time formats

[adhide]

when working out a time sheet for example, i want to be able to enter time
using a decimal point (.) instead of a colan) and be able to add them
together to get decimal hours.

ie. start 8.00 (8:00) finish 3.45 (15:45) minus .30minutes (0:30) = 7.25
(7:15)

 

[Fred Smith]

To convert hh.mm in a1 to an Excel time, use:

=time(int(a1),mod(a1,1)*100,0)

If you want to enter a time in a2 with an assumed pm, use:

=time(int(a2)+12,mod(a2,1)*100,0)

Your requested formula would then be:

=a2-a1-time(0,30,0)

Regards,
Fred.

 

[adhide]

if the assumed time is not PM format?

To convert hh.mm in a1 to an Excel time, use:

=time(int(a1),mod(a1,1)*100,0)

If you want to enter a time in a2 with an assumed pm, use:

=time(int(a2)+12,mod(a2,1)*100,0)

Your requested formula would then be:

=a2-a1-time(0,30,0)

Regards,
Fred.

 

[Teethless mama]

IF you want to enter time in decimal then enter like this:

Start time in A1: 8
Finish time in B1: 15.75 (not 3.45)

=B1-A1-0.5

 

[Fred Smith]

Then don't add 12.

Regards,
Fred.

if the assumed time is not PM format?

 

[KIM W]

Heads up:
When enteing time durations as hundreths of an hour you don't experience
different times for every hundreth of an hour.
For example, entering .24 or .23 will both display as 14 minutes. This is
rounding issue. There are a few others.