Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Subtract Decimal time
Hello from Steved
A formula for below please 9.30am from 12.25pm leaves 2.55 hrs Yes it is a decimal point Thankyou. |
#2
|
|||
|
|||
=INT((A1-B1)*24)+MOD((A1-B1)*24,1)*60/100
-- HTH RP (remove nothere from the email address if mailing direct) "Steved" wrote in message ... Hello from Steved A formula for below please 9.30am from 12.25pm leaves 2.55 hrs Yes it is a decimal point Thankyou. |
#3
|
|||
|
|||
Hello from Steved
Using the formula you have given me leaves -70.88 -----Original Message----- =INT((A1-B1)*24)+MOD((A1-B1)*24,1)*60/100 -- HTH RP (remove nothere from the email address if mailing direct) "Steved" wrote in message ... Hello from Steved A formula for below please 9.30am from 12.25pm leaves 2.55 hrs Yes it is a decimal point Thankyou. . |
#4
|
|||
|
|||
On Tue, 30 Nov 2004 16:16:01 -0800, "Steved"
wrote: Hello from Steved A formula for below please 9.30am from 12.25pm leaves 2.55 hrs Yes it is a decimal point Thankyou. If you don't mind installing the Analysis Tool Pak, =dollarfr(dollarde(A2,60)-dollarde(A1,60),60) or, more complicated, but without the ATP: =INT(INT(A2)-INT(A1)+(MOD(A2,1)-MOD(A1,1))*100/60)+ MOD(INT(A2)-INT(A1)+(MOD(A2,1)-MOD(A1,1))*100/60,1)*60/100 Later time is in A2, earlier time in A1 --ron |
#5
|
|||
|
|||
Thanks very much
-----Original Message----- On Tue, 30 Nov 2004 16:16:01 -0800, "Steved" wrote: Hello from Steved A formula for below please 9.30am from 12.25pm leaves 2.55 hrs Yes it is a decimal point Thankyou. If you don't mind installing the Analysis Tool Pak, =dollarfr(dollarde(A2,60)-dollarde(A1,60),60) or, more complicated, but without the ATP: =INT(INT(A2)-INT(A1)+(MOD(A2,1)-MOD(A1,1))*100/60)+ MOD(INT(A2)-INT(A1)+(MOD(A2,1)-MOD(A1,1))*100/60,1)*60/100 Later time is in A2, earlier time in A1 --ron . |
#6
|
|||
|
|||
Hello Ron From Steved
Please is it possible using your formula to get 12.20 from 3.17 will leave 2.57 thankyou. -----Original Message----- On Tue, 30 Nov 2004 16:16:01 -0800, "Steved" wrote: Hello from Steved A formula for below please 9.30am from 12.25pm leaves 2.55 hrs Yes it is a decimal point Thankyou. If you don't mind installing the Analysis Tool Pak, =dollarfr(dollarde(A2,60)-dollarde(A1,60),60) or, more complicated, but without the ATP: =INT(INT(A2)-INT(A1)+(MOD(A2,1)-MOD(A1,1))*100/60)+ MOD(INT(A2)-INT(A1)+(MOD(A2,1)-MOD(A1,1))*100/60,1) *60/100 Later time is in A2, earlier time in A1 --ron . |
#7
|
|||
|
|||
Hi SteveD,
Sorry, I read A1 and B1 as times, but I see these are decimal as well. This will work as long as you enter miltary time (15.17) =INT((A1-B1))+MOD((TIME(INT(A1),MOD(A1,1)*100,0)-TIME(INT(B1),MOD(B1,1)*100, 0)*1)*24,1)*60/100 -- HTH RP (remove nothere from the email address if mailing direct) "Steved" wrote in message ... Hello from Steved Using the formula you have given me leaves -70.88 -----Original Message----- =INT((A1-B1)*24)+MOD((A1-B1)*24,1)*60/100 -- HTH RP (remove nothere from the email address if mailing direct) "Steved" wrote in message ... Hello from Steved A formula for below please 9.30am from 12.25pm leaves 2.55 hrs Yes it is a decimal point Thankyou. . |
#8
|
|||
|
|||
On Tue, 30 Nov 2004 21:39:51 -0800, "Steved"
wrote: Hello Ron From Steved Please is it possible using your formula to get 12.20 from 3.17 will leave 2.57 thankyou. Yes, it is. If you make the assumption that A2 is ALWAYS going to represent a later time than A1, then you can modify the formulas by substituting for A2 the expression: A2+12*(A1A2). However, it might be simpler to use a 24 hr clock; such as 15.17 in your example. This would then allow you to span times across midnight. In any event, to answer your question, and assuming the two times represent times that are on the same day: =dollarfr(dollarde(A2+12*(A1A2),60)-dollarde(A1,60),60) or =INT(INT(A2+12*(A1A2))-INT(A1)+(MOD(A2+12*(A1A2),1)- MOD(A1,1))*100/60)+MOD(INT(A2+12*(A1A2))-INT(A1)+ (MOD(A2+12*(A1A2),1)-MOD(A1,1))*100/60,1)*60/100 --ron |
#9
|
|||
|
|||
Thankyou Ron for taking time to explain.
Cheers -----Original Message----- On Tue, 30 Nov 2004 21:39:51 -0800, "Steved" wrote: Hello Ron From Steved Please is it possible using your formula to get 12.20 from 3.17 will leave 2.57 thankyou. Yes, it is. If you make the assumption that A2 is ALWAYS going to represent a later time than A1, then you can modify the formulas by substituting for A2 the expression: A2+12*(A1A2). However, it might be simpler to use a 24 hr clock; such as 15.17 in your example. This would then allow you to span times across midnight. In any event, to answer your question, and assuming the two times represent times that are on the same day: =dollarfr(dollarde(A2+12*(A1A2),60)-dollarde(A1,60),60) or =INT(INT(A2+12*(A1A2))-INT(A1)+(MOD(A2+12*(A1A2),1)- MOD(A1,1))*100/60)+MOD(INT(A2+12*(A1A2))-INT(A1)+ (MOD(A2+12*(A1A2),1)-MOD(A1,1))*100/60,1)*60/100 --ron . |
#10
|
|||
|
|||
On Wed, 1 Dec 2004 10:15:11 -0800, "Steved"
wrote: Thankyou Ron for taking time to explain. Cheers You're welcome. I appreciate the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) | |||
Help - Information with time and date | Excel Discussion (Misc queries) | |||
decimal time subtraction please. | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions | |||
how do you subtract military time? example 12:00 - 07:00 = 5 hour. | Excel Worksheet Functions |