Hi,
I'm making a pilot logging system used to record flying hours. The system is being extended to include airframes.
Historical data is required, and so if a pilot happens to already have less than 9999 hrs flying, he's ok. The moment his historical record starts at 10000 hrs, excel cannot take that as a 'single' entry - you seem to need to put the data in as two lots of 500:00, for example, and then sum them.
Attached is a spreadsheet showing what works and what doesn't. tl;dr - 10000 hrs breaks the input.
Needless to say, entering hours and minutes, and THEN having to go to the formula bar and edit the ensuing date is not going to be a flier (pun definitely intended).
While it's probably an issue for some commercial pilots, it's definitely an issue for dealing with airframe data... eg take 35999 hrs and add 10000 hrs, and that's when an oil change is needed.
Also, it seems to break tallys in some formulae that go beyond 10000 hrs.
Has anyone else hit this problem ?
Thanks,
Brian
Hi Brian,
I used to fly gliders, so never reached 10000 hours.
Unfortunately, looks like excel will treat as text hours over 10000. I suggest using separate columns for hours and minutes and enter them in decimal system, you can easily convert them to time format: =A1/24+B1/24/60
Or, enter it like:=14000/24+TIME(9,23,45). Works, but not very easy to type.
Hi Catalin,
Thanks for that input, it cemented the thoughts I had about 'decimal minutes', and while getting stuff with separate hours and mins is a bit too much like a re-write at this point.
TBH - it was a bit of an unexpected gotcha, and it's making my 'spidey senses' twitch, the fact that it lucks out after '9999' hours 😉
I wonder if an excel developer would care to comment on how the entry is actually handled - my suspicion is some weird regex voodoo...
Anyway, having played with ensuring that you CAN edit the date stuff in the formula bar, and that the presentation of hours tally does go to 'silly numbers', I've come up with this idea:
Event handler looks into whether it needs to build a real date or do nothing at all...
I've not played with excel event handlers, and don't know what kind of performance hit they might make, but tbh with casual testing it seems to work.
Any one played with event coding in excel ?
I've included an updated spreadsheet with the following event function in place...
It only works to process the cell value when the user HAS ALREADY specified the custom format...
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo whoops
Dim theDate As Date
Dim newDate As Date
Dim numHours As Long
Dim numMins As Integer
If InStr(Target.NumberFormat, "[h]:mm;") > 0 And Target.Value >= 10000 Then
theTimeWanted = Split(Target.Value, ":")
theDate = DateValue("Jan 1, 1900")
numHours = Val(theTimeWanted(0))
numMins = Val(theTimeWanted(1))
' why it wants to add 48 hours is somebodies guess - not mine 😉
theDate = DateAdd("h", numHours - 48, theDate)
theDate = DateAdd("n", numMins, theDate)
Target.Value2 = theDate
End If
whoops:
On Error GoTo 0
End Sub
The cute thing about this is that it takes the 'hours' right to excels standard date limit: 31:12:9999
Out of interest, that's a little over 71,000,000 hours - recon the plane will have rusted to nothing by then 😉
Hi Brian,
No excel developer can tell you what microsoft engineers had in mind when they built the system. The science behind can be very complex, they just try to make it as simple as possible to users.
A few things you should consider:
1. If you type values larger than 10000:00 hours, excel will consider that entry as text, application.istext(target.Value) returns True. For values lower than 10000:00 hours, excel takes it as numeric and converts the number to decimal format, so 9000 will become 24 times smaller.
As an example, if you type 6000:00, target value will be 6000/24=250, not "6000:00"
If you type 12000:00, target.value will be the same entry treated as text and your comparison to 10000 becomes a text comparison, you have to convert text to a decimal value to compare it to 10000.
2. You should disable events when you write the result to cell, because this action will trigger another change event so your code will be called again when you write the result back to cell.
3. Target can contain more than one cell, code should loop through them all:
For each Cell in target.cells
..
Next
4. No matter if the cell is formatted or not as "[h]:mm", the Target.Text will return the entry as you typed it, no matter if greater or smaller than 10000 :"12000:00" or "500:00". To add more spice, this will not work as expected in cells where the format is "h:mm", on these the entry "500:00" gets converted to "20:00", full days are removed...
As suggested before, I would use normal decimal formats, in 2 columns: one for hours, one for minutes (restricted for numbers less than 60), join them in another column with A1&":"&B1 if you want to see them this way. This way, all the problems will disappear, you can work until the end of time... (if it's less than a 15 digit number...)
If you have lots of sheets, you can easily combine them into a single normalized sheet, from this sheet you can easily build reports for each pilot, group, or other attributes, it's a better way to do things.
