Formula to calculate Complex Date for Rental Equipment

Tina Calma

We have a column called "Rented Date". The rental week rate is based off of: 3 days equals a week (7 days), 3 weeks equal a month (28 days). We want to be able to calculate what the next date for the Week Date and Month Date.

Tina Calma

I finally figured out. This is what I was looking for:

=IF([Month Only]@row = 0, [Rented Date]@row + ((ROUNDUP((YEARDAY(TODAY()) - YEARDAY([Rented Date]@row)) / 28, 0)) * 28), DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1)

I needed the next billing cycle for the current (Today's) date, this will continue on several months. The formula above only gives the current billing cycle.

Brian Wilson DC

@Tina Calma Tina: I'm not sure if I am interpreting your request correctly, but you can use the foundation of what I've applied here to reach your end goal. Essentially, you pick the rental date and it either calculates a week out or a month out.

For the Next Week Date I am using the formula:

=IF([Type of Rental]@row = "Weekly (3 Days)", [Rented Date]@row + 7, " ")

For the Next Month Date I am using the formula:

=IF([Type of Rental]@row = "Monthly (3 Weeks)", [Rented Date]@row + 28, "")

Please let me know if you need any additional help.

Regards,

Brian

Tina Calma

I finally figured out. This is what I was looking for:

=IF([Month Only]@row = 0, [Rented Date]@row + ((ROUNDUP((YEARDAY(TODAY()) - YEARDAY([Rented Date]@row)) / 28, 0)) * 28), DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1)

I needed the next billing cycle for the current (Today's) date, this will continue on several months. The formula above only gives the current billing cycle.