time formats

  • Thread starter Thread starter adhide
  • Start date Start date
A

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)
 
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.
 
if the assumed time is not PM format?

Fred Smith said:
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.
Click to expand...
 
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
 
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.
 
Back
Top