+ Reply to Thread
Page 3 of 3 FirstFirst 1 2 3
Results 31 to 37 of 37

HELP - Duration/elapsed time with hours/minute stamp and leave blank if no data

  1. #31
    Registered User
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    MS Office 2010
    Posts
    85

    Re: HELP - Duration/elapsed time with hours/minute stamp and leave blank if no data

    Quote Originally Posted by GeoffW283 View Post
    As requested the attached file includes dropdown lists for departure and arrival airports.
    You can add or remove airports from the dropdown list simply by editing the "Destinations" worksheet. Time entry works exactly as before via Richard's macro.

    Duration in F11 copied down is:
    =IF(OR(C11="", E11=""), "", IF(E11-C11>=0,E11-C11,E11-C11+24))
    Layover in G11 copied down:
    =IF(C12="", "", C12-E11)
    I have copied and created my own spreadsheet like yours, used Richard's VBA macro. But it seems previously worked time formatting is not working. I cant enter times straight like 1350 and it wouldn't show as 13:50 PM or when I enter 0840 as 08:40 AM.

    What did I do wrong? Please help me. I have attached the file.
    Attached Files Attached Files

  2. #32
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    881

    Re: HELP - Duration/elapsed time with hours/minute stamp and leave blank if no data

    "Could you please simply explain how to use Richard's macro and apply it to my worksheet?"
    (1) See response (3) below.

    "Also how do you know when to use which formula like =IF(OR xxxxxx) and also how do you what things to write on Macro?? Where do you get or learn these?"
    (2) To learn the basics (or more, depending on your learning style) I would find a book or online tutorial that suits your style of learning. There's a whole host of recommended sources in this forum at: https://www.excelforum.com/excel-new...additions.html. Beyond that I personally learn best by working to solve specific problems: then, on this forum, when an expert/guru comes up with a better solution you learn again.

    "I have copied and created my own spreadsheet like yours, used Richard's VBA macro. But it seems previously worked time formatting is not working. I cant enter times straight like 1350"
    (3) You have placed the macro code in Module1. That is the wrong place for a macro that is responding to events, in this case a worksheet change event. The macro code needs to go on sheet1 instead.

    (4) I also noticed that you had not copied the F4 formula to F5 and have omitted the G4 and G5 layover formulas.

    I have attached your workbook with the above changes included.
    Attached Files Attached Files

  3. #33
    Registered User
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    MS Office 2010
    Posts
    85

    Re: HELP - Duration/elapsed time with hours/minute stamp and leave blank if no data

    Thank you Geoff and Richards. Much appreciated.

  4. #34
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    881

    Re: HELP - Duration/elapsed time with hours/minute stamp and leave blank if no data

    No problem - glad you now have what you need. Thanks for the feedback.

    If your problem is resolved then can you please mark the thread as solved. To do this select Thread Tools from the menu link above your first post and mark this thread as SOLVED. Thanks!

  5. #35
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    881

    Re: HELP - Duration/elapsed time with hours/minute stamp and leave blank if no data

    Thanks for the added reputation points - much appreciated!

  6. #36
    Registered User
    Join Date
    05-14-2015
    Location
    Ulaanbaatar, Mongolia
    MS-Off Ver
    MS Office 2010
    Posts
    85

    Re: HELP - Duration/elapsed time with hours/minute stamp and leave blank if no data

    Quote Originally Posted by GeoffW283 View Post
    Thanks for the added reputation points - much appreciated!
    How can I remove that error message when entering time? I want to enter text or something else. How can I disable or edit that somewhere else?? Plz help

  7. #37
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    881

    Re: HELP - Duration/elapsed time with hours/minute stamp and leave blank if no data

    I just commented out the first 3 lines of the error handler at the bottom of the VBA code.
    That still allows you to enter time as 3 or 4 digits as required but alse lets you enter text without causing an error or clearing out the text.

    See attached workbook.
    Attached Files Attached Files

+ Reply to Thread
Page 3 of 3 FirstFirst 1 2 3

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1