Rental: Months, Weeks, Days

Smidge18

New Member
Joined
Mar 4, 2018
Messages
17
Hi there: I'm trying to get the correct amount of months, weeks, and days for a vacation rental (I don't have one....just trying figure out how to do it). I'm using the following formula, with B being start date and C being end date. The formula almost works except it's short a day, as in not counting the first day as a rental day. For example: If B is Feb.25 and C is Feb.28, the answer will show 3 days instead of 4 days. I've tried adding +1 after the last parenthesis and that works until I get a total that would have been 6 days but now shows 7 days...not upping the 'weeks' number, and zeroing the 'days' number. I've been futzing with this for a couple days now and have read many threads, but none have worked so far. Any ideas? Thanks

=DATEDIF(B23,C23,"m")&" Months,"&ROUNDDOWN(DATEDIF(B23,C23,"md")/7,0)&" Weeks,"&MOD(DATEDIF(B23,C23,"md"),7)&" Days"

Of course is someone has a simple fix for this, then maybe someone else might know how to leave the cell blank when it shows 0 months,0 weeks,0 days. Thanks again.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
=DATEDIF(B23-1,C23,"m") &" Months, " &ROUNDDOWN(DATEDIF(B23-1,C23,"md")/7,0)&" Weeks,"&MOD(DATEDIF(B23-1,C23,"md"),7)&" Days"

Try this approach
 
Upvote 0
Thank you so much. Works perfectly. I was then able to separate Months, Weeks, Days, into separate columns, then hide the #NUM errors and the small error symbol. Spreadsheet looks great.
 
Upvote 0
You did all the heavy lifting, glad all I had to do was tweak it.

Thanks for the feedback

~DR
 
Upvote 0
Hi there: Just found a glitch. Everything looked perfect until I put Jan. 1 as the start date and March 1 as the end date. The result shows 2 months, 0 weeks, 5 days. It should be 2 months, 0 weeks, 1 day. It seems that anything from Jan 1 to almost anywhere in March isn't correct. Any ideas? Maybe Feb. being short is screwing things up?
 
Upvote 0
found the same error and no I am not sure why I will look around.
 
Upvote 0
Hi,
Chip Pearson’s site has an explanation on the use of the Datedif function and it has a section on Supressing Zero Values, which is one of the features you were after

Take a look here:-
http://www.cpearson.com/excel/datedif.aspx
 
Upvote 0
Interesting article, and thank you. I figured out a work-around. It may not be a formulaic fix, but a common sense one. I added a column: Originally, the columns were: 'From', 'To', which is where I was having issues of missing days. I added a 'Depart' column after the 'To' column and put the original formula under it. This seems to have fixed the issue. I've checked quite a few dates and it seems good....so far. If anyone has the time to double check this for me I'd really appreciate it. Thanks
 
Upvote 0
Sorry, made a mistake in my explanation. Column B is 'From', column C is 'To' , column D is 'Depart' , and column E is where the formula is: =DATEDIF(B23,D23,"m")&" Months,"&ROUNDDOWN(DATEDIF(B23,D23,"md")/7,0)&" Weeks,"&MOD(DATEDIF(B23,D23,"md"),7)&" Days"
I'm still checking and changing dates, but it looks like it's working now.
Thanks again for your input.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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
Back
Top