Formula logic

[planiolro]

Dear all,

In the cells B5 and C5(later date) I have two dates (m/d/y)

=C5-MIN(DATE(YEAR(C5),MONTH(C5)-(DAY(C5)< DAY(B5))+{1,0},DAY(B5)*{0,1}))

The formula above calculates the the number of days past the complete months. I kindly ask you to explain me the logic of this formula.
-What does it means {1,0}?Why it was used?
- DAY(B5)*{0,1})) How does it work this multiplication?
- What about C5-Min(date.....................

 

[barry houdini]

It's not difficult to calculate days or weeks, e.g. for days

=C5-B5+1

For weeks as a decimal

=(C5-B5+1)/7

or if you wanted a result like

5 weeks 4 days

=INT((C5-B5+1)/7)&" weeks "&MOD(C5-B5+1,7)&" days"

but if you try to convert this to months and days you'll end up with the same issues as before because some months are equal to 4 weeks only, some are equal to 4 weeks and 3 days.

I'm not saying that it's not possible to show a time difference in terms of months and days, just that if you do so you need to accept the inbuilt inconsistencies, you can't really escape them.

 

[asad]

Hello Barry,

I was wondering if "datedif" would fix this problem of finding days and months and years?

Asad