round up (date+45) to multiple of 5?
Hi all, I'm trying to write a formula that
- takes a date (C1)
- adds 45 days
- rounds the date UP to the 5th, 10th, 15th, 20th, or 25th
this is what I currently have
=IF(C1+45<=5, DATE((C1+45),(C1+45),5), IF(C1+45<=10, DATE((C1+45),(C1+45),10), IF(C1+45<=15, DATE((C1+45),(C1+45),15), IF(C1+45<=20, DATE((C1+45),(C1+45),20), IF(C1+45<=25, DATE((C1+45),(C1+45),25), IF(C1+45>25, DATE((C1+45),((C1+45)+1),5), error))))))
i am returning the following error: "Function DATE parameter 1 value 45743 is out of range."
where am I going wrong? is there a simpler/more elegant way to do this? TIA
Posted Feb 14, 2025, 3:14 PM UTC
14