Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time in Number Formate
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time in Number Formate
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 Replace @mailinator.com with @tiscali.co.uk wrote in message ups.com... 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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time in Number Formate
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 wrote in message ups.com... 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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating time in Number Formate
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert text number to number formate | Excel Discussion (Misc queries) | |||
Calculating Time with a number value | Excel Worksheet Functions | |||
calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Calculating effective time from start/end date+time | Excel Worksheet Functions |