• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Hours and Minutes with [h];mm; breaks when entering more than 9999:59 'thingies'...|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Hours and Minutes with [h];mm; breaks when entering more than 9999:59 'thingies'...|General Excel Questions & Answers|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Hours and Minutes with [h]…
sp_PrintTopic sp_TopicIcon
Hours and Minutes with [h];mm; breaks when entering more than 9999:59 'thingies'...
Avatar
Brian Jamieson

New Member
Members
Level 0
Forum Posts: 2
Member Since:
February 15, 2020
sp_UserOfflineSmall Offline
1
February 15, 2020 - 3:48 am
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1826
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
February 15, 2020 - 5:52 am
sp_Permalink sp_Print

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.

Avatar
Brian Jamieson

New Member
Members
Level 0
Forum Posts: 2
Member Since:
February 15, 2020
sp_UserOfflineSmall Offline
3
February 16, 2020 - 5:22 am
sp_Permalink sp_Print

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 😉

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1826
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
February 16, 2020 - 2:35 pm
sp_Permalink sp_Print sp_EditHistory

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.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Louis Muti
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Blair Gallagher
Brandi Taylor
Hafiz Ihsan Qadir
Gontran Bage
adolfo casanova
Annestine Johnpulle
Priscila Campbell
Jeff Mikles
Aaron Butler
Maurice Petterlin
Forum Stats:
Groups: 3
Forums: 24
Topics: 6369
Posts: 27852

 

Member Stats:
Guest Posters: 49
Members: 32359
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.