Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Span calculations
Cell A1 ~ 09:00 (Plan arrival time)
Cell A2 ~ 09:15 (Actual Arrival Time) Cell A3 ~ 0:15 (Late by 15Min) The above tells the timing for arriving to work and schedule to arrive to work. The calculation would be showing as such; ABS(A2-A1)=A3 My problem is, how do we get a negative. for example if Cell A1 ~ 09:00 Cell A2 ~ 08:55 This will tells that the arriving time is earlier by 5Min which should give -5min. how do I get a formula for such? Thanks in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Span calculations
Jafferi
Excel cannot display negative times. To display them in text format, you can use the below formula; but cannot be used for any calculations.. =IF(A2A1,"+" &TEXT(A2-A1,"hh:mm:ss"),"-"&TEXT(ABS(A2-A1),"hh:mm:ss")) If this post helps click Yes --------------- Jacob Skaria "Jafferi" wrote: Cell A1 ~ 09:00 (Plan arrival time) Cell A2 ~ 09:15 (Actual Arrival Time) Cell A3 ~ 0:15 (Late by 15Min) The above tells the timing for arriving to work and schedule to arrive to work. The calculation would be showing as such; ABS(A2-A1)=A3 My problem is, how do we get a negative. for example if Cell A1 ~ 09:00 Cell A2 ~ 08:55 This will tells that the arriving time is earlier by 5Min which should give -5min. how do I get a formula for such? Thanks in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Span calculations
You can test for - which one is the earlier time and have your calcualtions
accordingly... something like =IF(A1<A2,A2-A1,-(A1-A2)) "Jafferi" wrote: Cell A1 ~ 09:00 (Plan arrival time) Cell A2 ~ 09:15 (Actual Arrival Time) Cell A3 ~ 0:15 (Late by 15Min) The above tells the timing for arriving to work and schedule to arrive to work. The calculation would be showing as such; ABS(A2-A1)=A3 My problem is, how do we get a negative. for example if Cell A1 ~ 09:00 Cell A2 ~ 08:55 This will tells that the arriving time is earlier by 5Min which should give -5min. how do I get a formula for such? Thanks in advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Span calculations
By default, Excel for Windows doesn't display negative times/dates. If you
tried it then you noticed the result of the formula was a bunch of #### symbols. You can change a setting so that it will display negative times/dates *but* changing this setting will cause any current dates you have entered in your file to be off by 1461 days. For example, if A1 contained the date 1/1/2009 then you changed the date setting the date in A1 will now be 1/2/2013. For this reason I would reccomend not changing this setting. Unless you really know what you're doing, changing this setting can cause a lot of unforeseen problems. Having said all that... You can display the negative time in a TEXT format. That means it won't be a true numeric time value if you need to do further calculations on the result of the formula. A1 = 9:00 AM A2 = 8:55 AM =TEXT(ABS(A2-A1),IF(A2-A1<0,"-","")&"h:mm") Returns: -0:05 as a TEXT string -- Biff Microsoft Excel MVP "Jafferi" wrote in message ... Cell A1 ~ 09:00 (Plan arrival time) Cell A2 ~ 09:15 (Actual Arrival Time) Cell A3 ~ 0:15 (Late by 15Min) The above tells the timing for arriving to work and schedule to arrive to work. The calculation would be showing as such; ABS(A2-A1)=A3 My problem is, how do we get a negative. for example if Cell A1 ~ 09:00 Cell A2 ~ 08:55 This will tells that the arriving time is earlier by 5Min which should give -5min. how do I get a formula for such? Thanks in advance! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Span calculations
Hi,
If you only want minutes displayed then =1440*(A2-A1) and format the cell to the custom format General "Min" Format, Cells, Number tab, Custom. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Jafferi" wrote: Cell A1 ~ 09:00 (Plan arrival time) Cell A2 ~ 09:15 (Actual Arrival Time) Cell A3 ~ 0:15 (Late by 15Min) The above tells the timing for arriving to work and schedule to arrive to work. The calculation would be showing as such; ABS(A2-A1)=A3 My problem is, how do we get a negative. for example if Cell A1 ~ 09:00 Cell A2 ~ 08:55 This will tells that the arriving time is earlier by 5Min which should give -5min. how do I get a formula for such? Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
claculating time span | Excel Worksheet Functions | |||
Counting if Something Occurred During a Time Span | Excel Worksheet Functions | |||
Inconsistent excel 2007b2 time calculations that span whole days | Excel Discussion (Misc queries) | |||
Dividing a time span into shifts - overlapping days | Excel Worksheet Functions | |||
Time Span | Excel Discussion (Misc queries) |