Billing Date Calculator Template

[WindyCityAccountant]

Hoping someone can help me with a 'Billing Date Calculator' template I want to create.

I attached the excel file where I got most of the way there but need help, please see the attached file and the info below.

"Logic:

If a contract is signed between the 1st and the 15th of any month, then we begin billing on the 15th of the immediately following month.

If a contract is signed between the 16th and the end of any month, then we begin billing on the 1st of the month following the immediate month.

This means that the most we can go without billing is ~45 days and the shortest non billing period is ~30 days

Issue:

1) I've built the formula to work for 1 month, but I need it to account for the other date ranges highlighted in yellow so that I can enter any date throughout the year and it calculate for me. I believe I need and IFS statement instead of and IF statement, please help!"

[José Augusto]

Hi
Try This formula for a date in A3

Please Login or Register  to view this content.

[WindyCityAccountant]

Hi!

Thank you for your reply!

The formula that you gave appears to be working for the first piece of logic "If a contract is signed between the 1st and the 15th of any month, then we begin billing on the 15th of the immediately following month."

However it doesn't seem to work for the second piece of logic, " If a contract is signed between the 16th and the end of any month, then we begin billing on the 1st of the month following the immediate month (basically immediate month +1)."

For the second piece of logic, if the contract date is Jan 16th, it should bring back March 1st (currently the formula is bringing back Feb 1st like the first logic).

Would you mind helping to revise the formula to take into account the second piece of logic?

Thanks so much I wish I had these excel skills!

[Phuocam]

Try this:

=DATE(YEAR(A3),MONTH(A3)+1+(DAY(A3)>15),IF(DAY(A3)<=15,15,1))

Or:

=EOMONTH(A3,0+(DAY(A3)>15))+IF(DAY(A3)>15,1,15)

[WindyCityAccountant]

Thank you so much, this worked!