Decimal Time

[Steved]

Hello from Steved

I would like the formula to add 11.25 + 1.25 = 2hrs
I know 11:25 + 1:25 = 2hrs in [h]:m, but I would like to
add the times in Decimal format

Thankyou.

 

[Clarence Crow]

Hello from Steved

I would like the formula to add 11.25 + 1.25 = 2hrs
I know 11:25 + 1:25 = 2hrs in [h]:m, but I would like to
add the times in Decimal format

Thankyou.

Looks like 13:50 to me or are you a Martian?

Clarence

 

[J.E. McGimpsey]

I've got no idea how 11:25 + 1:25 = 2 (seems to me it = 12:50), but
you can add "times" with a decimal separator using:

A1: 11.25
A2: 1.25
A3: =INT(A1)+INT(A2) +INT((MOD(A1,1)+MOD(A2,1))/0.6)+
MOD(MOD(A1,1)+MOD(A2,1),0.6)

or

A3: =TEXT(TIME(INT(A1)+INT(A2), (MOD(A1,1) +MOD(A2,1))*100, 0),
"hh.mm")*1

though the latter works only for sums < 24.00

 

[J.E. McGimpsey]

Looks like 13:50 to me or are you a Martian?

11:25 + 1:25 = 13:50? Are you straddling a time zone?

 

[Steved]

Hello from Steved
Thankyou for your reply but I wordedv it all wrong

It should have read 11.25 + 1.25 to be caculated to add
to 2 Hours. ie 11.25 is am and 1.25 is pm time hence
11.25 to 12.00pm is 35 minutes and 1.25pm adds to 2 hrs

 

[Ron Rosenfeld]

Hello from Steved
Thankyou for your reply but I wordedv it all wrong

It should have read 11.25 + 1.25 to be caculated to add
to 2 Hours. ie 11.25 is am and 1.25 is pm time hence
11.25 to 12.00pm is 35 minutes and 1.25pm adds to 2 hrs

-----Original Message-----
Hello from Steved

I would like the formula to add 11.25 + 1.25 = 2hrs
I know 11:25 + 1:25 = 2hrs in [h]:m, but I would like to
add the times in Decimal format

Thankyou.
.

=dollarde(13.25,60)-dollarde(11.25,60)

I expressed 1.25 as 13.25 as you did not give a rule to tell whether a time is
AM or PM, but that rule could be incorporated into the formula if you would
supply it.

If the DOLLARDE function is not available, and returns the #NAME? error,
install and load the Analysis ToolPak add-in.


--ron

 

[Ron Rosenfeld]

Ron thanks for your reply, I require it to be written as
11.25, in A1 and 1.25 in B1. 11.25 is AM and 1.25 is PM.
I am producing a spreadsheet with this type of time
format for my company as when you type in a few times per
day for a timetable it is quicker, I would for example
put a formula that tells cell A1 to replace . (Decimal)
with : but I am only learning to do functions, and I
would not know if this is possible

Note that if you replace the decimal with a colon, you will be dealing with
Excel times and, if you want a decimal result, you will have to multiply your
answer by 24. Excel stores times as fractions of a day, so you still need some
rule to tell AM from PM. If the rule is that a time in A1 is AM and a time in
B1 is PM, then

=dollarde(B1+12,60)-dollarde(A1,60)

will give you a result of 2

If you want to convert to Excel times, then you could use:

=(TIMEVALUE(SUBSTITUTE(B1+12,".",":"))-TIMEVALUE(SUBSTITUTE(A1,".",":")))*24

which would result in a value of 2.

or if you don't have the analysis toolpak installed, you could use:

=B1+12+MOD(B1,1)/0.6-(A1+MOD(A1,1)/0.6)


But you do need to be clear about differentiating AM from PM. And also whether
you want the result to be in Decimal or in the Excel time format.


--ron

 

[Harlan Grove]

Ron thanks for your reply, I require it to be written as
11.25, in A1 and 1.25 in B1. 11.25 is AM and 1.25 is PM.

Is the first value *always* AM and the second *always* PM? If not, you have
an extremely difficult problem with *machine* interpretation of these times.
You'd need to define *very* *precise* rules for when to interpret such
entries as AM or PM.

I am producing a spreadsheet with this type of time
format for my company as when you type in a few times per
day for a timetable it is quicker, I would for example
put a formula that tells cell A1 to replace . (Decimal)
with : but I am only learning to do functions, and I
would not know if this is possible

What you're after is time *differences*, not adding times. So since periods
can only be positive,

=1.25-11.25+12*(1.25<11.25)

would take care of this particular problem. But there's still a problem with
periods of more than 12 hours, such as 7:00 AM to 9:00 PM.

 

[Steved]

Thanks Ron.

-----Original Message-----

Note that if you replace the decimal with a colon, you will be dealing with
Excel times and, if you want a decimal result, you will have to multiply your
answer by 24. Excel stores times as fractions of a day, so you still need some
rule to tell AM from PM. If the rule is that a time in A1 is AM and a time in
B1 is PM, then

=dollarde(B1+12,60)-dollarde(A1,60)

will give you a result of 2

If you want to convert to Excel times, then you could use:
(SUBSTITUTE(A1,".",":")))*24

which would result in a value of 2.

or if you don't have the analysis toolpak installed, you could use:

=B1+12+MOD(B1,1)/0.6-(A1+MOD(A1,1)/0.6)


But you do need to be clear about differentiating AM from PM. And also whether
you want the result to be in Decimal or in the Excel time format.


--ron
.

 

[Harlan Grove]

I've got no idea how 11:25 + 1:25 = 2 (seems to me it = 12:50), but
you can add "times" with a decimal separator using:

A1: 11.25
A2: 1.25
A3: =INT(A1)+INT(A2) +INT((MOD(A1,1)+MOD(A2,1))/0.6)+
MOD(MOD(A1,1)+MOD(A2,1),0.6)

....

Or stealing from Dave Peterson, who claims to have stolen from someone else,

=TEXT((TEXT(100*11.25,"00\:00")+TEXT(100*1.25,"00\:00")),"[h].mm")

as text, or

=--TEXT((TEXT(100*11.25,"00\:00")+TEXT(100*1.25,"00\:00")),"[h].mm")

as numeric.