Running Dates based on variables

[riskier4ra]

Hi everyone,

I have an interesting challenge if anyone is willing to take a look at it. Ive been working on this for several hours, and thought I had it worked out but....

Maybe you beautiful minds can help?

I am trying to get a date that resolves from a another date that is inputted to always be 6 months out but land on a specific day, the 7th to be exact.

Thanks for any help offered,

D

 

[Matty]

Hi,

If your start date is 01/01/2018, what would you expect the result to be? 07/06/2018?

What about 10/01/2018? Would this be 07/07/2018 or still be 07/06/2018?

Matty

 

[riskier4ra]

Hi,

If your start date is 01/01/2018, what would you expect the result to be? 07/06/2018?

What about 10/01/2018? Would this be 07/07/2018 or still be 07/06/2018?

Matty

Hi Matty,

If the start date is December 2nd 2017 the resolved date would be April 7th 2018

There is a kicker. If the start date is after the 10th day or higher it would be push the resolved date to the next month So if December 10th 2017 the resolved date would be May 7th 2018.

I came up with something like this. IF(DAY(O10>=3),DATE(M10,N10+7,$B$3),DATE(M10,N10+6,$B$3)) It was close, but the >=3 part of it kept having to be changed to be higher than the day of the first date which keeps me from being able to just pull down the formula as I move down rows.

M N 0 are helper columns I created to hold Year Month Day (respectively) from the start date because I couldnt get the formula to extract the month day and year data. B3 is the fixed day of the month.

Thanks,
D

 

[Tetra201]

Maybe just

=DATE(M10,N10+6+(O10>9),$B$3) if you want to use the helper columns.

Or

=EOMONTH(A10,5+(DAY(A10)>9))+$B$3 if you don't want to use them and your date is in cell A10.

 

[riskier4ra]

Maybe just

=DATE(M10,N10+6+(O10>9),$B$3) if you want to use the helper columns.

Or

=EOMONTH(A10,5+(DAY(A10)>9))+$B$3 if you don't want to use them and your date is in cell A10.

Hi Tetra,

The EOMONTH is a big help I wanted to get rid of the helper columns, but the logic produces the same problem I was having with my formula. Below is sample dates using =EOMONTH(A2,6+(DAY(B2)>9))+$F$2. What you can see is in bold 10/2/2017 end date should be 4/7/2018 but the formula for some reason makes it 5/7/2018. If you make (A2,5+ like you suggested, it throws more dates off than just the one. Hope this helps.

<colgroup><col style="mso-width-source:userset;mso-width-alt:4022;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:3657;width:75pt" width="100"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4242;width:87pt" width="116"> <col style="width:48pt" span="2" width="64"> </colgroup><tbody>
[TD="class: xl64, width: 110"]Start Date
[/TD]
[TD="class: xl64, width: 100"]End Date[/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 116"]Formula Date should Equal Column B
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Fixed Day[/TD]

[TD="class: xl66"]7/5/2017[/TD]
[TD="class: xl68"] 2/7/2018
[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]2/7/2018[/TD]

[TD="class: xl64"] 7
[/TD]

[TD="class: xl66"]7/21/2017[/TD]
[TD="class: xl66"]2/7/2018[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]2/7/2018[/TD]

[TD="class: xl66"]7/24/2017[/TD]
[TD="class: xl66"]2/7/2018[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]2/7/2018[/TD]

[TD="class: xl66"]9/11/2017[/TD]
[TD="class: xl66"]4/7/2018[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]4/7/2018[/TD]

[TD="class: xl67"] 10/2/2017
[/TD]
[TD="class: xl67"] 4/7/2018 [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"] 5/7/2018 [/TD]

[TD="class: xl66"]10/10/2017[/TD]
[TD="class: xl66"]5/7/2018[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]5/7/2018[/TD]

[TD="class: xl66"]10/23/2017[/TD]
[TD="class: xl68"] 5/7/2018
[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]5/7/2018[/TD]

[TD="class: xl66"]11/16/2017[/TD]
[TD="class: xl66"]6/7/2018[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]6/7/2018[/TD]

[TD="class: xl66"]11/21/2017[/TD]
[TD="class: xl68"] 6/7/2018
[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]6/7/2018[/TD]

[TD="class: xl66"]12/7/2017[/TD]
[TD="class: xl66"]7/7/2018[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]7/7/2018[/TD]

[TD="class: xl66"]12/28/2017[/TD]
[TD="class: xl66"]7/7/2018[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]7/7/2018[/TD]

[TD="class: xl66"]1/10/2018[/TD]
[TD="class: xl66"]8/7/2018[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"]8/7/2018[/TD]

</tbody>

 

[Matty]

Hi,

I'm not sure I understand the logic being applied to achieve the results in your table. For example, with a start date of 05/07/2017 (07/05/2017 in UK), 6 months on from this would be 01/07/2018, yet you're showing 02/07/2018.

For clarity, could you specify how many months are being added to each start and the reason why the number is what it is?

Matty

 

[Matty]

*Corrected*

Hi,

I'm not sure I understand the logic being applied to achieve the results in your table. For example, with a start date of 07/05/2017 (05/07/2017 in UK), 6 months on from this would be 01/07/2018, yet you're showing 02/07/2018.

For clarity, could you specify how many months are being added to each start and the reason why the number is what it is?

Matty

 

[Tetra201]

... The EOMONTH is a big help I wanted to get rid of the helper columns, but the logic produces the same problem I was having with my formula. Below is sample dates using =EOMONTH(A2,6+(DAY(B2)>9))+$F$2. What you can see is in bold 10/2/2017 end date should be 4/7/2018 but the formula for some reason makes it 5/7/2018. If you make (A2,5+ like you suggested, it throws more dates off than just the one...

That's because your modified formula is not correct. The formula for the dataset from Post # 5 should be:

=EOMONTH(A2,5+(DAY(A2)>9))+$F$2

I highlighted the erroneous elements.

In addition, the dataset from Post # 5 has two miscalculations in column B:

7/5/2017 --> 2/7/2018 (should be 1/7/2018)
12/7/2017 --> 7/7/2018 (should be 6/7/2018)

 

[riskier4ra]

That's because your modified formula is not correct. The formula for the dataset from Post # 5 should be:

=EOMONTH(A2,5+(DAY(A2)>9))+$F$2

I highlighted the erroneous elements.

In addition, the dataset from Post # 5 has two miscalculations in column B:

7/5/2017 --> 2/7/2018 (should be 1/7/2018)
12/7/2017 --> 7/7/2018 (should be 6/7/2018)

Hi Tetra,

I modified the cell locations to create the table for the example. My apologies if that confused you.

I agree with your logic, its the same as mine, but it doesnt fit. Those are not miscalculations according to a creditor that gives 6 months no interest terms.

These are the dates that are on a spreadsheet statement from this creditor, a very large one, so its safe to assume this is generated, not someone just typing up statements as they produce millions of these a month, and the same logic is applied across 12 months of statements.

I am trying to figure out their logic and apply it to my own spreadsheet so that I can forecast. It doesn't make sense, I agree with you. That is why I reached out to the excel community to see if we could figure out how they are calculating the expiration date. I figured I was missing something that maybe someone else would catch or be able to figure out.

The statement close date is the 10th of every month, the pay date is the 7th of every month.

I hope his clarifies things a little better.

 

[riskier4ra]

*Corrected*

Hi,

I'm not sure I understand the logic being applied to achieve the results in your table. For example, with a start date of 07/05/2017 (05/07/2017 in UK), 6 months on from this would be 01/07/2018, yet you're showing 02/07/2018.

For clarity, could you specify how many months are being added to each start and the reason why the number is what it is?

Matty

Hi Matty,

See the response to Tetra above. Thanks for the help!