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

does the attached answer some of your questions.
torachan.

Originally Posted by torachan

does the attached answer some of your questions.
torachan.

Not quite what I was looking for. Hehe but it's nice touch. How do you do that data enter box?

Originally Posted by Richard Buttrey

In the code there is a line

Please Login or Register  to view this content.

This is saying carry on with the macro if the cell you're changing is anywhere in column A. It's the Range("A:A") you need to change to refelect your situation

But instead of us all guessing, why don't you upload the actual workbook so that we can be uite specific and add the code for you.

Sorry. I have attached the workbook. Can you please insert the macro/code you created onto the actual workbook? Thank u so much.

@Byam
Please don't quote entire posts unnecessarily. They are just clutter and make threads hard to read.
Use the "Quick reply" button instead.

then remove the quote option? why not use implemented feature?

How do I leave elapsed/duration time cell empty/blank if there are no time data?? Using this formulate to calculate elapsed time - =TEXT(E12-C12,"hh:mm"). Is there any formulate to add on this?

In post #1 point 2 you are looking to have durations formatted like: 4h30m
You could do this by simplifying the above formulas to remove the text() function and instead use a custom format for col-F as follows:
h"h"mm"m"

The above formulas would now become:

Formula:

Please Login or Register  to view this content.

or (to accommodate layover row):

Formula:

Please Login or Register  to view this content.

Also, is there a possibility that the departure - arrival times span midnight? If so then a further change to the above formulas would be necessary:
Either:

Formula:

Please Login or Register  to view this content.

or (to accommodate layover row):

Formula:

Please Login or Register  to view this content.

How can I enter times without having to write colon? Like an example I want to write straight 1345 and want it to be shown as 13:45 pm or 1:45 pm. But now, whenever I type in time 1345 it shows 0:00 (showing 9/6/1903 12:00:00 AM on formula bar)

Is this possible to do? Thank you.

Originally Posted by Byambadorj

Yes there will be a times where flights and arrivals will span midnight (i.e. departs at 21:30 PM on 2019/05/20 and arrive midnight 03:20 AM on 2019/05/21) how would I get this data correct? In this case, do I have to insert full date and time?
Can you take a look at my actual workbook and add please?

Thanks for the feedback on #1 & #2 - much appreciated.

For #3: The last 2 formulas I provided in post #13 do accommodate flights that span midnight and durations should be calculated correctly using these formulas. That said, I'll take a look at your attached workbook and comment further if necessary.

Originally Posted by Byambadorj

How can I enter times without having to write colon? Like an example I want to write straight 1345 and want it to be shown as 13:45 pm or 1:45 pm. But now, whenever I type in time 1345 it shows 0:00 (showing 9/6/1903 12:00:00 AM on formula bar)
Is this possible to do? Thank you.

Richard in post #3 had provided VBA code to do this. I'll look at your recent attached workbook and see if I can add. Note that this will mean that you will have to have a macro enabled workbook - are you and your company OK with that?

Yes, its okay. Do I need to copy+paste that code by opening fresh VBA blank page and close excel -> save as macro-enabled workbook? Is that all or do I need to run it every time I open the workbook? Thank you, much appreciated.

The attached itinerary_revA.xlsx is derived from your post #14 book3.xlsx with the following tweaks:

• I added Richard Buttrey's post #3 macro and tweaked it to apply only to ranges C11:C15 and E11:E15. You should be able to see in the VBA how that has been done so that you can adjust those ranges if needed. I also tweaked the macro to ignore errors which could occur if, for example, you had to move the Layover row - the macro expects numbers!. In col-C and col-E you can now write times in 24 hour format as hhmm or hmm which I think is what you wanted.
• In book3.xlsx you have entered full date and time for departure and arrival. That would remove the need to address midnight spans in the "duration" formula, but I thought you didn't want to do that, so I updated the col-F with the formulas I provided in my post #13 that automatically account for midnight spans. This approach is also compatible with the use of Richard's macro and your requirement to enter times like hhmm.
• I moved the layover time back to the "duration" column or the col-E macro overwrites the formula!

Thats all I did.

All that said, dates and times and formatting and date/time arithmetic are confusing enough without adding macros into the mix. Hopefully as you update the worksheet for different itineraries we haven't caused maintainability problems here.

Finally - note that you must have macros enabled for the above to work.

Let us know if the attached itinerary_revA.xlsx does what you want or if you have further questions.

Comparing Richard's post #19 file with my post #20 file:

My file is based on Byambadorj's latest provided file, book3.xlsx from post #14, has the required formatting for the Duration column and deals correctly with departure/arrival times that span midnight.

Richard's file is based on a prior Byambadorj post, book2.xlsx. It has a more elegant approach to defining the ranges to which the macro will be applied and it implements more robust error handling.

Originally Posted by GeoffW283

The attached itinerary_revA.xlsx is derived from your post #14 book3.xlsx with the following tweaks:

• I added Richard Buttrey's post #3 macro and tweaked it to apply only to ranges C11:C15 and E11:E15. You should be able to see in the VBA how that has been done so that you can adjust those ranges if needed. I also tweaked the macro to ignore errors which could occur if, for example, you had to move the Layover row - the macro expects numbers!. In col-C and col-E you can now write times in 24 hour format as hhmm or hmm which I think is what you wanted.
• In book3.xlsx you have entered full date and time for departure and arrival. That would remove the need to address midnight spans in the "duration" formula, but I thought you didn't want to do that, so I updated the col-F with the formulas I provided in my post #13 that automatically account for midnight spans. This approach is also compatible with the use of Richard's macro and your requirement to enter times like hhmm.
• I moved the layover time back to the "duration" column or the col-E macro overwrites the formula!

Thats all I did.

All that said, dates and times and formatting and date/time arithmetic are confusing enough without adding macros into the mix. Hopefully as you update the worksheet for different itineraries we haven't caused maintainability problems here.

Finally - note that you must have macros enabled for the above to work.

Let us know if the attached itinerary_revA.xlsx does what you want or if you have further questions.

This looks perfect. But a question rises again.

Departure and Arrival can be either Ulaanbaatar to Seoul or Beijing. I had them lined in two rows because there can be times it will be one of them. And then I considered layover from Seoul or Beijing to Phuket. In that case, how would I calculate the layover time correctly?

At the moment it doesn't seem to calculate the layover at Beijing (C15) to Phuket (E15). Also, why does the layover calculation formula include E13 which is blank and serves no purpose? Layover duration must be shown on F13.

Thank you very much for what you are doing Geoff and Richard. Please take a look again?

Current layover formula calculates layover between Seoul to Phuket. It's not calculating layover between Beijing to Phuket. Is it possible to add it to existing layover calculation or add another row and place it there?

Richard - The reference to E13 is in itinerary_revA.xlsx cell F13 where I calculate the layover time. I was trying to have the same formula all the way down the "Duration" column. Now I'm not sure whether the benefit is worth the added complexity. I have merged your latest VBA updates into the attached file.

Byambadorj - The layover time in the attached update is now calculated correctly for the route via Seoul and the roiute via Beijing. The F13 formula in fact is now much simpler - however, that is because it is specific to this particular itinerary. In F13:

Formula:

Please Login or Register  to view this content.

Presumably you want to use this template for other routes? So just as Richard has requested in post #23 can you provide further info? For example, how would this template look for an itinerary, say with two layovers? Will you often have alternate routes on the same template? If so how many? What would the template look like if there were 3 route options, one direct, one with 1 layover, one with 2 layovers?

Another thought: Would a layout like the following work for you?

itinerary_proposal.png

I have eliminated the unused alternate route from the itinerary and compensated for that by providing a dropdown list of destinations for ease of entry. "Layover" now gets its own column.

This I think is a more flexible layout and leads to simpler and more logical formulas and hence easier maintainability.

Originally Posted by GeoffW283

Richard - The reference to E13 is in itinerary_revA.xlsx cell F13 where I calculate the layover time. I was trying to have the same formula all the way down the "Duration" column. Now I'm not sure whether the benefit is worth the added complexity. I have merged your latest VBA updates into the attached file.

Byambadorj - The layover time in the attached update is now calculated correctly for the route via Seoul and the roiute via Beijing. The F13 formula in fact is now much simpler - however, that is because it is specific to this particular itinerary. In F13:

Formula:

Please Login or Register  to view this content.

Presumably, you want to use this template for other routes? So just as Richard has requested in post #23 can you provide further info? For example, how would this template look for an itinerary, say with two layovers? Will you often have alternate routes on the same template? If so how many? What would the template look like if there were 3 route options, one direct, one with 1 layover, one with 2 layovers?

The departure location will not change and it'll remain Ulaanbaatar but the route may change between Ulaanbaatar to Seoul and Phuket or Ulaanbaatar to Beijing and Phuket. That means it will only have 1 layover only.

Regarding your latest post with drop down list. It looks really good and practical, also easy to follow. Could you attach it together with the formulas? Thank you.

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)

Originally Posted by GeoffW283

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)

Thank you for this. Could you please simply explain how to use Richard's macro and apply it to my worksheet?

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? Please help me. I really, really wanna learn these.

Originally Posted by GeoffW283

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.

"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.

Thank you Geoff and Richards. Much appreciated.

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!

Thanks for the added reputation points - much appreciated!

Originally Posted by GeoffW283

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

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.