Updating Dates in Excel

[Tygra_1983]

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 if there is anyway I can update these dates once the day has arrived and the money is taken out? For example, something comes out on the 2nd of July. Once the 2nd of July comes the date will automatically be updated to the 2nd of August. Can this be done?

Thanks,

[#2]

@Tygra_1983 You don't really give away much but perhaps the attached workbook inspires you to find a solution.

[#3]

@Riny_van_Eekelen 

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.

[#4]

@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.

[#5]

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.

Thanks

[#6]

@Tygra_1983 

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.

[#7]

Thank you, Mathetes, for clearing that up for me.

[#8]

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 again

[#9]

@Tygra_1983 

You 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.

[#10]

Yes, Mathetes, a daily reduction by one.

[#11]

Again, it is similar to before. The countdown I was referring to is the "Days to go" column.

[#12]

Can you post the actual spreadsheet from which that image was taken. An image can't be tested, explored, modified: it can only be viewed.

[#13]

@mathetes 

Here

[#14]

So I have to ask you what you're looking for. It already gives the countdown based on start date and today's current date. So what is it not doing that you want it to? And what's the relationship between this new spreadsheet and its functionality and the one that you originally posted with the question that Riny answered?

I also (from a design point of view) want to ask why you have a separate set of columns altogether for income (credits) vs expenses (or debits). From a design perspective, it would generally be better to have them using the same columns where those columns mean the same thing (e.g., amount, due date, payee/payor) and just have a single column with a code differentiating between those that are credit and those that are debit. It's easy enough to write a formula (an IF formula, as it happens) that maintains the running balance (assuming that's an objective) by subtracting debits and adding credits even though the numbers are in the same "Amount" column.

[#15]

Mathetes, once the countdown of' Days to Go' reaches zero, it then preceeds to go -1 days -2 days etc. What I would like to do is once the days reaches zero and the direct debit is paid, for the original number to return. For example, if the bill is paid every 14 days, I would like the 'Days to Go' to return to 14 days after reaching zero.

As for your advice I will take it. it does sound more logical. I'm a beginner with Excel, so I'm looking to learn.

[#16]

@Tygra_1983 

So what you really need (let me suggest) is a way to capture:

• the fact that you've paid this monthly obligation -- i.e, you need somewhere in this workbook to record history of bills paid, checks written, etc.
• the date each month when standing bills are due--i.e., you need a table with monthly obligations, payee, amount and date of the month each of those standing payments is due 

And THEN, based on those, a formula that calculates the days remaining, every month, for each of those payments.

If that makes sense -- if you'll be adding to this spreadsheet a more complete history, resembling a check register, then we can create a model for what that would look like and how it might work.  Let me ask, though, before we devote time and energy to show you a possible design....whether that's what you plan. You've been coming here asking questions that are just a small part (I'm assuming) of what could be--should be--a much bigger project. Will you be wanting to add, say, budget categories to this effort to get on top of your finances?

Have you looked at the templates that Excel already has for check registers and the like? It's not necessary, and personally I often like to go design my own (for the learning it forces), but it would help me and others help you if you could tell us more about what your vision or hope is here with this start at a spreadsheet to manage your finances.

[#17]

@Tygra_1983 Why not incorporate my original solution, with a few tweaks, into your own schedule. For every recurring item, you will need to enter the first due date. This will, in principle, not change. Then you calculate the next due date, similar to how I did in my 2nd post. Now you can calculate the days to go by deducting today's date from the next date.

See attached. Not that I created named ranges for cells C2 and D2. This is just to make the formulae that refer to these cells easier to read and maintain. Then, you'll note columns I and J. I've shaded these green to indicate that these are helper columns. Press the minus button above column K to hide them.

[#18]

Riny, how would I change the formula in the K column if I wanted update every 14 days or 7 days? As opposed to updating every month?

[#19]

@Tygra_1983 

Could I ask you, to help both @Riny_van_Eekelen and me to help you, by spelling out a bit more fully the big picture here. Your big picture.  

Although we can always give you an answer on how to do a specific formula, the more we know about the full context, what the big picture is, the more we can help with design and layout as well. Creating a budget spreadsheet, for example, or a check register, or whatever it is that you're trying to do...there will be multiple steps involved, multiple types of data you'll be collecting and organizing, and a clear goal in mind. The goal could be simply to make sure you pay all obligations on time; maybe that is ALL you're trying to do.

But even that involves several assumptions that you may be making. Like this 14 or 7 days request: WHAT is that really about? How does it bear on the "Start date" that you've carefully collected for each obligation? If it doesn't having any bearing, why enter it? Etc.

The series of questions you asked don't seem related to one another, which is why I'm asking. So I'm not sure that answering with a new formula each time is really helping when we don't have a coherent picture of the full purpose.

[#20]

@mathetes 

Hi Mathetes,

Apologies for not giving you a clear picture of what I am trying to do. At the moment though all I want to achieve is to have the information of dates when my direct debit or incomes come out. I just quickly want to see what dates my direct debits come out and a countdown in days to the dates. On my internet banking I need to know in my head this information, or write it down on paper which is more tedious. I just want to be able to open Excel and see "ah, my rent is due is 8 days, 7 days 1day etc.."

The furthest I have come by my own efforts is to create the formula "=IF(I4=C2,I4+14,I4)". I4 being the starting due date. C2 being =TODAY() and 14 being 14 days tell my next payment. However, this will only work for the first payment after the I4 starting date. So I feel completely stuck on how to fix this. Hence, why am using Riny's formula and asking how to change it for 14 days or 7 days.

Hope that helps.