=TEXT(D1-D2,"d+h+m") not working if days is over 1 month?

[bowlesj]

Hi,

The formula in the subject line is giving the wrong number of days when the span of time is over a month.

My attached picture contains two snapshots.

• The first snapshot shows my highlighting the formula in the subject line so you can see it in the formula bar. Here the days is the extra days across a month (4 days rather than the number of actual days).
• The second snapshot shows where I copied the set of cells down and I changed the formula to include the month. This is the revised formula

  =TEXT(D6-D7,"m+d+h+m") & " Months + Days + Hours + Minutes since extraction."

  . Again I highlighted it so you can see it in the formula bar. Here the months is incorrect. It should be 1 month.

How might I get the actual total days? Once I have that I can figure out the weeks. Actually "W+D+H+M" would be best if that is possible. I tried "w+d+h+m" and that just showed "w+4+7+3".

Thanks,
John

 

[SpillerBD]

Your images suggest you are actually working in the worksheet and not VBA.
Converting to Text limits any referencing of the cell for further calculations.
With =D6-D7, you should get a result of 35.3388888888876
That is 35 Days, the integer, and the remainder represents the part of the day or 8:008:00, or 8 hours and 8 minutes.
You can use MOD to break out the time, and use INT to capture the number of days.

Days= Int(D6-D7)
HoursMinutes = Mod(D6-D7)

 

[bowlesj]

Thanks, SpillerBD. With your help I managed to get the correct results using this.
=INT($D$1-$D$2) & "+" & TEXT($D$1-$D$2,"h+m") & " Days + Hours + Minutes since extraction."
It will be interesting to see if this holds up over time.
If you are wondering what extraction means it is my tooth extraction

 

[Toadstool]

Hi Bowlesj,

I was answering your duplicate question when the thread was closed. Here's my solution to that question:

"Hi Bowlesj,

Try

=INT((D6-D7)/7)&" weeks, "&INT(MOD((D6-D7),7))&" days, "&TEXT(D6-D7-(INT(D6-D7)),"[h]")&" hours and "&RIGHT(TEXT(D6-D7-(INT(D6-D7)),"hh: m"),2)&" minutes since  extraction"

"

 

[bowlesj]

Thank you Toadstool.

I put it in and I got it to work. It has been a long day. I will study your solution tomorrow for a better understanding after some sleep when my mind is up to spec .

John