Forum Discussion
Updating Dates in Excel
Thanks
Actually, the IF part of Riny's formula is a simple IF; it's the other parts that need explaining.
First, some of the references in the formula:
- $E$1 refers to the cell that contains today's date. That cell uses a function, TODAY(), that's very useful; it always returns today's date and can be used in a variety of ways.
- The "Day" column is used to specify the day in each month when those debt payments are due.
- D5, with the 5 being incremented by 1 on each subsequent row, simply refers to the Day column for the debt in question.
- DAY function returns the number of the day from any full Excel date
- EOMONTH determines the end of any given month from a date that is entered
So here's how all that adds up in a simple IF function
- =IF(D5>=DAY($E$1), if D5 (the day each month the debt is due) is greater than or equal to today's date (the date in $E$1),
- $E$1+D5-DAY($E$1), then leave the date as is for this current month. The math there is tricky until you think about it. Today's date ($E$1) plus the day in the month for this debt minus the day of the month that is today's day. So 6/23/21 plus 27 minus 23 = 6/27/21
- EOMONTH($E$1,0)+D5) else add the due date number to the end of the current month, to get the due date next month.
- Thank you, Mathetes, for clearing that up for me.
- Thanks again guys for all your help. However, I would like to ask another question: how do I perform a calculation where once the value reaches zero it automatically resets to the original value. Like say there is a countdown from 14, once it reaches 0 it automatically goes back to 14? What formula would you use?
Thanks againYou asked: how do I perform a calculation where once the value reaches zero it automatically resets to the original value. Like say there is a countdown from 14, once it reaches 0 it automatically goes back to 14? What formula would you use?
To which I have to respond, it all depends. There's not a single answer, unfortunately. It depends on how the data are arrayed, how that countdown itself occurs and how it's displayed. Is it a daily reduction by one, a reduction by one every time "X" occurs; is it even a reduction by one, or can the countdown be more random? You see what I mean?!
You'd probably need a helper column again, but as noted so very much depends on the rest of the process.
=IF(X=0,Y=14,Y=X) or some variation might work, but, again, it all depends.
Do you have a spreadsheet going where this needs to be resolved? If so, post a copy.