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

#### bowlesj

##### New Member
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
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
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
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

##### Well-known Member
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"``
"

#### bowlesj

##### New Member

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

Replies
1
Views
82
Replies
2
Views
876
Replies
6
Views
46
Replies
3
Views
162
Replies
1
Views
47

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.

### Which adblocker are you using?

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

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