round up (date+45) to multiple of 5?

Unsolved

Hi all, I'm trying to write a formula that

  1. takes a date (C1)
  2. adds 45 days
  3. 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
Loading comments…