MoneySavingExpert Chair, Martin Lewis · Editor, Marcus Herbert

# 'Simple' Excel spreadsheet - date next due

Posts: 443 Forumite
Hello
I would like to create a spreadsheet to organise my work round.  Column 1 would be date last actioned, column 2 would be 'frequency' ie 4 weeks, 6 weeks, 2 months, 3 months and column 3 would be 'Date next due'.

In the frequency column I have created 'drop down lists to chose one of the interval options.  Can someone suggest a formula that I can use to complete the 'Date next due'?

So date completed is say '15/12/2021', frequency is '4 weeks'. how do I get excel to calculate the 'date next due' as '12/01/2022'?

I hope you can help

• Posts: 1,826 Forumite
• Posts: 31,851 Forumite
edited 27 December 2021 at 5:34PM
You simply need to add the correct number of days - cell+number of days - or months - EDATE(cell,number of months) - to the first date.  Multiple IFs based on the drop down box text. Something like, for C1
=IF(B1="4 weeks",A1+28,IF(B1="2 weeks",A1+14,IF(B1="2 Months",EDATE(A1,2))))

• Posts: 443 Forumite
Thanks Molerat.

Does Excel recognise 'month' as a quantity if you use 'edate'?
Do I need to specify the data type for the 'date next due date' to 'date'?
• Posts: 31,851 Forumite
edited 20 December 2021 at 12:53PM
Do I need to specify the data type for the 'date next due date' to 'date'?
That is purely a heading, it is not a data type, could be "sausages" for all it matters. All you need to do is ensure the cells are formatted to show the date in the format you wish to see.  In Excel date is simply a number displayed as a recognisable date and EDATE increases that number accordingly also incrementing the year when necessary.
• Posts: 443 Forumite
Just re-read the formula and it makes sense now, just tried it out and it works, thank you very much!

Being cheeky, I might push it a bit by asking how I can stop the lines that do not yet have a 'date last actioned' (i.e blank) from saying 'FALSE' in the 'date next due' column?

• Posts: 5,349 Forumite
edited 22 December 2021 at 2:50PM
Just re-read the formula and it makes sense now, just tried it out and it works, thank you very much!

Being cheeky, I might push it a bit by asking how I can stop the lines that do not yet have a 'date last actioned' (i.e blank) from saying 'FALSE' in the 'date next due' column?

Can you arrange the formula  to give a zero value instead of false? Then you can use the display or hide zero values option to give you a blank cell.

PS: You will probably not be able to do the above in one cell because the IF() function to get the zero, will be referencing the same cell as the AND() function is referencing..  You will have to have the AND() and IF() functions in different cells.  This is OK if you are able to hide the cell with the AND() function by hide row or hide column or put it off to the side or top or bottom of the table.
• Posts: 31,851 Forumite
edited 22 December 2021 at 4:27PM
Just re-read the formula and it makes sense now, just tried it out and it works, thank you very much!

Being cheeky, I might push it a bit by asking how I can stop the lines that do not yet have a 'date last actioned' (i.e blank) from saying 'FALSE' in the 'date next due' column?

Change the formula to
=IF(ISBLANK(A1),"",IF(B1="4 weeks",A1+28,IF(B1="2 weeks",A1+14,IF(B1="2 Months",EDATE(A1,2)))))
so it checks if the date last actioned is empty before doing anything else.  If you clear the date last actioned cell at any time you need to "clear contents" and not just overwrite with the space bar as that does not clear the cell but leaves a space and will return FALSE.

• Posts: 4,035 Forumite
Just use Calendar??
No longer a spouse, or trailing, but MSE won't allow me to change my username...
• Posts: 443 Forumite
Just use Calendar??
How do I do that exactly?
• Posts: 443 Forumite
molerat said:
Just re-read the formula and it makes sense now, just tried it out and it works, thank you very much!

Being cheeky, I might push it a bit by asking how I can stop the lines that do not yet have a 'date last actioned' (i.e blank) from saying 'FALSE' in the 'date next due' column?

Change the formula to
=IF(ISBLANK(A1),"",IF(B1="4 weeks",A1+28,IF(B1="2 weeks",A1+14,IF(B1="2 Months",EDATE(A1,2)))))
so it checks if the date last actioned is empty before doing anything else.  If you clear the date last actioned cell at any time you need to "clear contents" and not just overwrite with the space bar as that does not clear the cell but leaves a space and will return FALSE.

Thanks again Molerat.  very informative

#### Categories

• All Categories
• 343.2K Banking & Borrowing
• 250.1K Reduce Debt & Boost Income
• 449.7K Spending & Discounts
• 235.3K Work, Benefits & Business
• 608K Mortgages, Homes & Bills
• 173.1K Life & Family
• 247.9K Travel & Transport
• 1.5M Hobbies & Leisure
• 15.9K Discuss & Feedback
• 15.1K Coronavirus Support Boards