Forum Discussion
Updating Dates in Excel
Hi there, I am doing a worksheet for my finances. I have a column with my direct debits and the dates within the next month that they will be taken out of my account. What I would like to know is...
Tygra_1983 You don't really give away much but perhaps the attached workbook inspires you to find a solution.
I mean for the date to change automatically once the 'Days to Go' = 0. So once the 'Days to Go' = 0, 28 days will be added on automatically.
Tygra_1983 I would introduce a helper column with the day number of the direct debit and then use a formula to calculate the next due date.
A quick-and-dirty solution in the attached file.
Edit: Uploaded the wrong file. Corrected now.
- Thanks Riny_van_Eekelen. But could you explain the IF formula you have used in the E column? I'm not so advanced with Excel. I can only use simple IF formulas.
ThanksActually, 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.
