EXCEL datetime addition: how to display month in cell

[kwmkwm2]

Hi, in my spreadsheet I am subtracting a lesser datetime from a greater datetime in order to get an elapsed time (E7-E5) across several columns. These values (example: 07:20/2017 15:57 - 07:20/2017 10:49 = value 0.213888889) are then added (=sum(E10+F10+J10 etc), the result in this case is 0.22.

I then format the result using =TEXT(B25, "dd:hh:mm:ss) and get the result I expect, 00:05:24:00. 5 hours, 24 minutes. When I try to prefix a month in the reformatting (=TEXT(b25,"mm:hh:mm:ss") which I will eventually need, I always get the value "01", as in 01:00:05:24:00.

My question is, how do I get the month to display?
K

 

[MrKowz]

Welcome to the forums!

I believe what you're going to need to do is pull the month from one of your cells which contain the month. Dates and times are represented as a whole number of days from 1/0/1900 and a decimal number of the time of the day. For example, today (August 2, 2017) is the value 42949. The current date and time (August 8, 2017 9:22 AM (CST)) is 42949.39076678. The 0.39076678 represents the time 9:22 AM. The 42949 represents today's date.

When you do the calculation 07:20/2017 15:57 - 07:20/2017 10:49, you're effectively stripping off the date portion of the value, leaving only a time. If you try to display that as a date/time, you'll notice it is going to show 1/0/1900 hh:mm (whatever that hh:mm calculates out to) and which is why you're always getting "01" returned as month.

What we need to do is grab the month from one of the source cells. You mention you use E7-E5 to get the time calculation. Try:

=TEXT(MAX(E5, E7), "mm")&":"&text(B25,"hh:mm:ss")

This will pull the month from the latest date in E5 or E7 and append it to the hh:mm:ss calculation which is being returned in B25.

Hope this helps!

 

[kwmkwm2]

Thanks, Mr. K
Your code worked, but I didn't explain myself well enough.

The end intent of the code I have is to add up all the elapsed time. My idea was that eventually due to overflow(?) I'd want to display the overflow value in DAYS in the next bucket (which in my mind was weeks or months). As seconds become minutes, minutes become hours, etc. Not sure that this makes sense to or is doable in excel.


so if I had 2 tasks, one that had a elapsed time of 20 days, 10 hours, 5 minutes, 6 seconds, another that had a elapsed time of 15 days, 10 hours and 5 minutes, 6 seconds and I added them together, my result could be either:

(1) 35:20:10:12 or (Days,hours,minutes,seconds)
(2) 01:15:10:10:12. (Months, Days,hours, minutes, seconds)

Either is fine, but I was trying for the 2nd one. If it's overly complex then (1) is good enough.
K

Welcome to the forums!

I believe what you're going to need to do is pull the month from one of your cells which contain the month. Dates and times are represented as a whole number of days from 1/0/1900 and a decimal number of the time of the day. For example, today (August 2, 2017) is the value 42949. The current date and time (August 8, 2017 9:22 AM (CST)) is 42949.39076678. The 0.39076678 represents the time 9:22 AM. The 42949 represents today's date.

When you do the calculation 07:20/2017 15:57 - 07:20/2017 10:49, you're effectively stripping off the date portion of the value, leaving only a time. If you try to display that as a date/time, you'll notice it is going to show 1/0/1900 hh:mm (whatever that hh:mm calculates out to) and which is why you're always getting "01" returned as month.

What we need to do is grab the month from one of the source cells. You mention you use E7-E5 to get the time calculation. Try:

=TEXT(MAX(E5, E7), "mm")&":"&text(B25,"hh:mm:ss")

This will pull the month from the latest date in E5 or E7 and append it to the hh:mm:ss calculation which is being returned in B25.

Hope this helps!

 

[MrKowz]

Is this more like what you're trying to accomplish? (note, I'm using the decimal representation of days/time in here. 20.42 = 20 days 10 hours 5 minutes 6 seconds). This also assumes 30 days constitutes a "month".

Excel 2013/2016
	A	B	C	D
1	Inputs		Result	Formatted Result
2	20.42		35.84	01:05:20:10:12
3	15.42
Sheet1

Cell Formulas
Range	Formula
C2	=A2+A3
D2	=TEXT(INT(C2/30),"00") & ":" &TEXT(INT(C2-(INT(C2/30)*30)),"00") &":"&TEXT(C2-INT(C2),"hh:mm:ss")

 

[kwmkwm2]

Mr. K,
That was perfect, thanks much!
K

 

[MrKowz]

Mr. K,
That was perfect, thanks much!
K

Glad it works out for you. Thanks for the feedback, and have a great day!