time formats

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

adhide - 13 Mar 2008 02:52 GMT

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)

Reply to this Message

Fred Smith - 13 Mar 2008 03:53 GMT

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.

> 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)

Reply to this Message

adhide - 13 Mar 2008 04:02 GMT

if the assumed time is not PM format?

> To convert hh.mm in a1 to an Excel time, use:
>
[quoted text clipped - 17 lines]
> > ie. start 8.00 (8:00) finish 3.45 (15:45) minus .30minutes (0:30) = 7.25
> > (7:15)

Reply to this Message

Fred Smith - 13 Mar 2008 14:55 GMT

Then don't add 12.

Regards,
Fred.

> if the assumed time is not PM format?
>
[quoted text clipped - 21 lines]
>> > 7.25
>> > (7:15)

Reply to this Message

Teethless mama - 13 Mar 2008 04:28 GMT

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

Reply to this Message

KIM W - 13 Mar 2008 15:27 GMT

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.

Reply to this Message