Formula logic

planiolro

Board Regular
Joined
Oct 30, 2008
Messages
104
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.....................
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Note the dates and then format B5:D5 General
Use Formula Auditing shortcut possibly Alt TUF
Step through the formula with Evaluate

review =C5-DATE(YEAR(C5),MONTH(C5)-(DAY(C5) < DAY(B5)),DAY(B5))
-(DAY(C5) < DAY(B5) if Day C5 < Day B5 it deducts 1 from month

and then review =C5-MIN(DATE(YEAR(C5),MONTH(C5)-(DAY(C5) < DAY(B5))+{1,0},DAY(B5)*{0,1}))
 
Last edited:
Upvote 0
That's the "day" part from the formula I posted here

The formula already gives years and months and that part just calculates the number of days remaining.

Here's a simple example:

B5 = 4th February 2008
C5 = 27th April 2009

The difference is 1 year and 2 months.....then the formula you posted here gives the number of days. Clearly here the answer is 23 so to get that the MIN part of the formula returns the date 4th April 2009 and that's subtracted from the date in C5 to get 23.

All we want to calculate then, with the MIN part, is the day from B5, i.e. the 4th combined with the year and month from C5, but what about where the dates are like this?

B5 = 27th February 2008
C5 = 4th April 2009

When the day of C5 is less than the day of B5 I need to go back a month, i.e. I want to find the day of B5 (the 27th) in the Year and Month of C5 (minus 1 month), i.e. 27th March 2009.

Those 2 possibilities lead to this formula:

=DATE(YEAR(C5),MONTH(C5)-(DAY(C5)< DAY(B5)),DAY(B5))

which will give the correct date for the above two examples......

.....but there are other more complex possibilities, what if my 2 dates were these?

B5 = 31st January 2008
C5 = 13th March 2009

Now the above formula will give 3rd March which I don't want because then the difference will be "1 year, 2 months and 10 days".....but I need to count from 28th February to get 13 days.

That's where the {0,1} comes in.

If you use a simple formula like this

=10+{1,0} that generates an array like this {11,10}, i.e. 10 added to both elements of the "array constant". Similarly this formula

=10*{0,1} generates an array like this {0,10}, i.e. 10 multiplied by each element of the array....

so going back to the DATE formula when B5 and C5 are as follows

B5 = 31st January 2008
C5 = 13th March 2009

then this formula

=DATE(YEAR(C5),MONTH(C5)-(DAY(C5)< DAY(B5)),DAY(B5))

turns into this

=DATE(2009,3-1,31)

which, as stated before, is equivalent to 3rd March 2009.....but when I use my "array constants" in conjunction with MIN I ensure that the date doesn't "spill over" into the next month, i.e.

=MIN(DATE(2009,2+{1,0},31*{0,1}))

is equivalent to

=MIN(DATE(2009,{3,2},{0,31}))

This formula will give the MIN of the two dates. The two dates are DATE(2009,3,0), i.e. 28th February 2009 and DATE(2009,2,31), i.e. 3rd March 2009.

HTH, barry
 
Upvote 0
Your formula (days past completed months) is working excellent in the majority of the cases. However I have one cases where the formula is not precise:
31.01.09 and 14.09.09 - obviously here we have 7 completed months and 15 days (14 in september and 1 in january). However your the result of your formula says that there are only 14 days.
Another example
01.02.09 and 14.09.09 - the correct answer is 7 completed months and 14 days (only the days in September) but your formula says only 13.
What cand be done in order to be sure of the results. In this way I have to check the entire data-base which is time consuming.

Thank you for your time and effort
 
Upvote 0
31.01.09 and 14.09.09 - obviously here we have 7 completed months and 15 days

I don't agree with your maths, the calculation doesn't include both start and end date so 31st Jan to 31st August is exactly 7 months, result for additional days is zero, therefore to 1st September is 1 day, to 2nd Septemebr is 2 days etc. so the 14th is 14 days
 
Upvote 0
Mathematically speaking you are right. My current workplace forces me to take into consideration both beginning and date. According to the number of days spent on oil platform you are entitled to a number of leave days. I have been searching on the internet a way to solve this but I haven’t found anything! Up to now your formula gives in majority of the cases the exact result. Can you help me in this regard to indicate me a formula which would take into account both the beginning and ending day?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
Thank for your time and effort.
 
Upvote 0
Typically you either need to add 1 to C5 or subtract 1 from B5. For the formula you quoted here it would be easier to subtract 1 from B5, i.e.

=C5-MIN(DATE(YEAR(C5),MONTH(C5)-(DAY(C5) < DAY(B5-1))+{1,0},DAY(B5-1)*{0,1}))
 
Upvote 0
I have tried your formula. when I used the substraction I got negative numbers. On the other hand when I added 1 to C5 I did not get any negative numbers but still there are some errors.
Examples with C5 +1:
- 31.01.09 and 14.09.09 - 7 months and 15 days - it worked
- 26.09.08 and 14.09.09 - 11 months and 19 days - I got 20 days instead of 19
The following example is quite intresting:
31.01.08 and 13.03.09 - 13 months and 14 days - both formulas(the formula you initially posted and the one with C5 +1) gave the right answers.

What can be done to find a reliable formula?
 
Upvote 0
What can be done to find a reliable formula?

You are trying to measure a time period using units that don't have a consistent length, i.e. years and months. If you want absolute consistency then use a unit of time that doesn't vary, e.g. weeks or days.

If you must use years and months then you need to decide upon the rules that you want to apply.

The formulas I suggested both work consistently. The last example you give, for instance, gives the same result for both formulas because February has 28 days.

13 months from 31st Jan will give you 31st Feb, which doesn't exist so it gives the last day of Feb, i.e. the 28th....but when you use B5-1, which is 30th Jan then 13 months from that date is still 28th Feb.

If you choose to calculate some other way (but still using years and months) then you can probably find a way round that problem but you'll only move the issue somewhere else.

If you go back to the thread where I first posted that formula (as a substitute for DATEDIF) you can see from other posts that whatever the method used there will still be inconsistency because it's impossible with months and years to find a formula that will consistently add a day to the result every time you add 1 to end date or subtract 1 from the start date.

For example.....

You want to count both start and end date so 1st Feb 2009 to 28th Feb 2009 is exactly 1 month. Logically if I start a day earlier and end a day earlier the result should still be one month, shouldn't it?

...but do that and you find that 31st Jan 2009 to 27th Feb 2009 is also one month, which surely isn't correct? Isn't 28th Jan to 27th Feb 1 month, but that's 3 days shorter......:confused:
 
Upvote 0
So is there any formula which makes a calculation taking into consideration weekes and days and after that to convert them in months and years? Is this a resonable question?:confused:
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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