Rental: Months, Weeks, Days

[Smidge18]

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.

 

[Drrellik]

=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

 

[Smidge18]

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.

 

[Drrellik]

You did all the heavy lifting, glad all I had to do was tweak it.

Thanks for the feedback

~DR

 

[Smidge18]

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?

 

[Drrellik]

found the same error and no I am not sure why I will look around.

 

[Smidge18]

OK, thank you. I'm still trying to figure a way around this as well.

 

[Taul]

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

 

[Smidge18]

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

 

[Smidge18]

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.