Time Between Two Dates and Times

[TINHAMMER]

I am trying to calculate the time between two future dates and NOW in the format dd/hh or XX days and YY hours. The three dates and times are in a custom format mm/dd/yy hh:mm;@. I have attached a Microsoft Excel worksheet which illustrates what I am asking. Cells F6 and F7 are the two cells I would like to format.

Any thoughts on how to do this?

Thanks in advance for any help I might receive.

[MrShorty]

Using formatting alone, I am not aware of any way to display a "decimal days" number (the current values in F6 and F7) in any kind of sexagessimal "elapsed days and hours" format.

Are you interested in solutions that will convert the result to a text string? That will separate the "days" and "hours" portion of the result into separate cells? How would you like to proceed recognizing that this cannot be achieved using number formatting alone?

[TINHAMMER]

A solution that would separate the days and hours into two separate cells would OK with me.

[DJunqueira]

3 ways to format the cell "[h]:mm:ss", "d hh:mm" and "d m hh:mm" they all have some limitations explained inside the worksheet.
I also use two formulas to calculate days and time, =DATEDIF($B$5,$B6,"d") and =MOD(F6,1)

[MrShorty]

Since Excel's date time serial number system is built on "days", the number of elapsed days is simple the integer portion of the current result. So, you can either format F6:F7 as integer "0", or you can use the INT() or ROUNDDOWN() or FLOOR() functions to round the result down to the nearest day.

The fractional/decimal portion of the result represents the fraction of the day. You can simply use a format like "hh" (hours would be truncated rather than rounded, so you may need to decide if you want rounded hours or not) or "hh:mm:ss" on a copy of the result. You can use the MOD() function to extract the fractional portion, then convert to decimal hours or whatever you want to do.

If you have questions about implementing some of these different ideas:
1) Leave the formula in F6 alone, format as "0". In G6, enter =F6 and format G6 as "hh" (remember that hours here is rounded down to the nearest hour).
2) Leave the formula in F6 alone, format as "0". In G6, enter =CONVERT(MOD(F6,1),"day","hr") and format as "0" (rounds up or down to the nearest hour).
3) Edit formula in F6 to be =INT(current formula). In G6, enter =CONVERT(MOD(B6-B$5,1),"day","hr") and format as "0".

That should give you 3 different options, and there are many others depending on what exactly you decide you want from this. Does any of that help?

[TINHAMMER]

Thank you for your response. I understand the results in F6 and F7 also G6.

I do not understand the format used cells L6 and L7 - d m hh:mm.

I believe the hh:mm refers to hours and the mm refers to minutes I am a loss as to what the d and m represent. Can you explain this to me?

Thanks

[MrShorty]

In date/time number format codes, "d" refers to calendar day for the underlying serial number, and "m" refers to calendar month. One "m" tells Excel to display the month as a number (1 to 12). Excel does not have an "elapsed days with sexagessimal time" formatting option, which is the limitation that DJunqueira noted in the file. "d" means 0 to 31 (for January 1900), then starts over at 1 for Feb 1900. "d" formatting can only mean "elapsed days" if the number of days will always be less than 31.

[DJunqueira]

In date/time number format codes, "d" refers to calendar day for the underlying serial number, and "m" refers to calendar month. One "m" tells Excel to display the month as a number (1 to 12). Excel does not have an "elapsed days with sexagessimal time" formatting option, which is the limitation that DJunqueira noted in the file. "d" means 0 to 31 (for January 1900), then starts over at 1 for Feb 1900. "d" formatting can only mean "elapsed days" if the number of days will always be less than 31.

Tks, saved me time.

[MrShorty]

Another thought -- especially if displaying only hours is important and you won't ever need to see the minutes -- you could use the DOLLARFR() function to return a "days.hours" result. =DOLLARFR(B6-B$5,24) [format as "0.00" to show only two decimal places] will return (rounded to two decimal places) 21.17 in F6 and 42.17 in F7 -- meaning 21 days 17 hours and 42 days 17 hours. This approach will not extend to showing minutes, nor does it work very well as an intermediate calculation (because you would need to get back to the decimal representation using the DOLLARDE() function). But, it does provide a simple way to get to a final days.hours result, if that is what you need.

[DJunqueira]

Another formula that could work well.

Please Login or Register  to view this content.

[janmorris]

to format as d.h (visually it will appear decimal) then use Custom format, and then use d.h

[janmorris]

when the days are greater than would be in a month, then you can use a formula like this (in G6, then drag down):

Please Login or Register  to view this content.

[TINHAMMER]

Thank you for your response. This is the first time this senior citizen has worked with the Date Time function. I thought the NOW function updated on a continuous basis, like every second. I found out that is not the case.

Now that I understand the difference between two cells format in date time is in days as you told me, I have developed what I think is a solution. I have attached a worksheet which shows what I think might be a solution.

Let me know if you see any problems.

[janmorris]

sorry, but none of the calculations are correct.

i am working to correct the formulas as i type....

[TINHAMMER]

Wow, I must NOT understand how the date time function works I can't believe you can't multiply the days difference times 24 to get hours difference. I am looking forward to your response.

[janmorris]

my apologies, i see what you are doing with the cells. your formulas are correct for the layout.

[TINHAMMER]

Thanks, I was worried this self-taught Excel amateur really blew it. All of my calculations are based on the difference between two cells in the date time format, is in days with the decimal portion representing hours, minutes, and seconds. I am not that familiar with some of the cell formatting suggested, but I did what I thought would work.

Thanks for looking what I prepared. I am still learning about Excel.

[janmorris]

when looking at the times, then the values i had clicked on something in between so the values changed (due to the NOW) and didnt match the time i had in my mind and at the same time the calculations above used a different method to what i use (my method calculates each one independently without helper cells), so i was completely thrown off.

[TINHAMMER]

Thanks, I learned how NOW operates when I put this work sheet together. I thought it was like a clock that constantly changed with time. I found that was not the case. I am going to explore some more options regarding date and time so I have a better understanding of how these functions work in Excel.

Again, thanks for critiquing my work.

[MrShorty]

This might help understand dates and times in Excel: http://www.cpearson.com/Excel/datetime.htm

[TINHAMMER]

Thank You, that is the best explanation I have read to date which explains Date/Time formatting. I am confident that the formulas I have used in my worksheet are correct. However, I used the DOLLARFR() function you suggested in Thread Number 9 - 12/13/2021 to check. I also used the formatting from DJunqueira in Thread Number 10 - 12/13/2021 06:48 PM as another check.

Thanks for everyone's input.