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

bowlesj

New Member
Joined
Aug 28, 2011
Messages
19
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
    VBA Code:
    =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
 

Attachments

  • Excel_Issue.png
    Excel_Issue.png
    35.2 KB · Views: 12

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,722
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

New Member
Joined
Aug 28, 2011
Messages
19
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

Well-known Member
Joined
Mar 5, 2018
Messages
2,197
Office Version
  1. 2016
Platform
  1. Windows
Hi Bowlesj,

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

"Hi Bowlesj,

Try
Excel Formula:
=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"
"
 
Solution

bowlesj

New Member
Joined
Aug 28, 2011
Messages
19
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
 

Forum statistics

Threads
1,148,176
Messages
5,745,191
Members
423,931
Latest member
thangvan114

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top