Hi there, I'm new here and I'm hoping someone can help. I'm creating a spreadsheet to record survey data in. It's airline stuff and I'm trying to be smart and calculate the difference between two times so I can say whether the flight took off early, was on time or was late. I've simply subtracted one cell from the other which, where the flight was on time is fine the formula chucks back 00:00 or where the flight was late it shows 00:42 but where it was early i've got loads of ######### because it won't show time as being a negative I think.
Anyone know how i can show it? I would then like to put a conditional colour format on showing early as green, on time as green and red as late.
Any help would be gratefully received. Thank you
Using the 1900 Date System it is not possible (as you can see) to show negative time value in a Time format. If you format the cell to General you will see the underlying value.
There are fixes ranging from
a) switching date system (ill advised IMO)
to
b) storing the time values as Decimal, ie * result by 24 and formatting as General (ie 3:30 becomes 3.5, -0:30 becomes -0.5 etc)
to
c) storing values as Text and dummying the text string so as to closely resemble time format.
Which you should use will largely depend on whether or not you need to do additional calculations with resulting values.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks