Calculating time in Number Formate

[write2bhatt]

Hi,

I have Created a formula for the users who want to calculate time into
number format.

Example
Anirban want to calculate time spent in Xerox Audit between 7:00 AM to
9:26 AM. Now using simple subtraction formula he will get 1:26 but if
he want to see the result in number formate which shows 1.26, he can
the following formula

=(TRUNC((((B1-A1)*(24*60))/60),0))+((((((B1-A1)*(24*60))/60)-
(TRUNC((((B1-A1)*(24*60))/60),0)))*60)/100)

 

[Sandy Mann]

Is this solving a problem before it arises?

But what happens if you add add up these *times* in number format?

1:26
1:26
1:26

SUMs to 4:18 but:

1.26
1.26
1.26

SUMs to 3.78 which is not correct.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Peo Sjoblom]

1. I don't see the point using a decimal value that does not really reflect
the time?
The real decimal value if needed for calculations is

=(B1-A1)*24

formatted as general


2. The difference between 7:00 and 9:26 is not 1:26 it is 2:26



3. You can use a custom format of hh.mm if it is just for display



4. If you really want to get 1.26 you can use a simpler formula like

=HOUR(B1-A1)+MINUTE(B1-A1)/100

or if there can be more than 24 hours

=INT((B1-A1)*24)+MINUTE(B1-A1)/100


all formula results formatted as general


--


Regards,


Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

I have Created a formula for the users who want to calculate time into

number format.

Example
Anirban want to calculate time spent in Xerox Audit between 7:00 AM to
9:26 AM. Now using simple subtraction formula he will get 1:26 but if
he want to see the result in number formate which shows 1.26, he can
the following formula

=(TRUNC((((B1-A1)*(24*60))/60),0))+((((((B1-A1)*(24*60))/60)-
(TRUNC((((B1-A1)*(24*60))/60),0)))*60)/100)

Doesn't this much shorter and easier to remember formula do the same thing?

=--TEXT(B1-A1,"h.mm")

By the way, your posted subtraction was wrong; it should have been 2:26, not
1:26. I have a question, though. Why would you want to show the time as if
it were a floating point number? I mean, 2.26 hours(?) is not the same as
2:26 in hours and minutes, so why would anyone need this particular
conversion?

Rick