Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Calculations
How can you calculate times??
Initially looking through the help file it shows the following function =TEXT(H1-I1,"h:mm") which calculates the diffence in hours and minutes between two times, however if the values are 00:09:00 and 00:10:00 it won't calculate it give a #VALUE if the values are 00:10:00 and 00:09:00 it will calculate What am I doing wrong Thanks in advance Jai |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Calculations
IF you're using the WinXL default 1900 date system, you can't display
negative times, so TEXT() returns the #VALUE! error. If you use the 1904 date system (Tools/Options/Calculation...) you can display negative times using TEXT(). NOTE that TEXT() is not necessary, you can use =H1-I1 by itself, then use Format/Cell/Number/Custom h:mm to display the result in the way you want. In article , Jai_Friday wrote: How can you calculate times?? Initially looking through the help file it shows the following function =TEXT(H1-I1,"h:mm") which calculates the diffence in hours and minutes between two times, however if the values are 00:09:00 and 00:10:00 it won't calculate it give a #VALUE if the values are 00:10:00 and 00:09:00 it will calculate What am I doing wrong Thanks in advance Jai |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Calculations
Dates and times in excel are stored as numbers - the date part being an
integer and the time part being a decimal (enter a date and time in excel and the n change the formatting to number ot see an example). The TEXT function simply converts a number to a string based on the format you supply. So what your function is actually doing is simply taking one number from another and then returning the result as a string formatted as h:mm. When you put the earlier time before the later time, the result will be negative, since the decimal increases through the day (0.25 = 06:00, 0.5 = 12:00 etc). So the reason it returns an error is because you are asking it to format a negative number as h:mm, which as far as it is concerned is nonsense. So the trick is to return the ABSOLUTE difference between the times, then it won't matter whether you put the earlier time first of second (assuming that is what you want to do). You can acheive this using the ABS function, e.g.: =TEXT(ABS(H1-I1),"h:mm") Personally, I think that format is a bit confusing so I'd play around with something like: =TEXT(ABS(H1-I1),"h ""hours"", m ""minutes""") The double quotes allow you to enter whatever text you like. Rob Jai_Friday wrote: How can you calculate times?? Initially looking through the help file it shows the following function =TEXT(H1-I1,"h:mm") which calculates the diffence in hours and minutes between two times, however if the values are 00:09:00 and 00:10:00 it won't calculate it give a #VALUE if the values are 00:10:00 and 00:09:00 it will calculate What am I doing wrong Thanks in advance Jai |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Calculations
Note that it's only nonsense using the 1900 Date system. It works fine
in the 1904 system... In article .com, "Rob Hick" wrote: So the reason it returns an error is because you are asking it to format a negative number as h:mm, which as far as it is concerned is nonsense. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Calculations
JE McGimpsey wrote: Note that it's only nonsense using the 1900 Date system. It works fine in the 1904 system... I didn't realise that - thanks. I still prefer the solution where it doesn't matter which setting is chosen; changing settings to make things work has a tendency to come round later and bite you on the ass! In article .com, "Rob Hick" wrote: So the reason it returns an error is because you are asking it to format a negative number as h:mm, which as far as it is concerned is nonsense. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
perform calculations using time arguments | Excel Worksheet Functions | |||
Time calculations | Excel Worksheet Functions | |||
Time Calculations | Excel Worksheet Functions | |||
time interval calculations in excel | Excel Discussion (Misc queries) | |||
Date and time calculations | Excel Discussion (Misc queries) |