'Simple' Excel spreadsheet  date next due
doningtonphil
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
Thanks ain advance
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
Thanks ain advance
0
Comments

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))))
0 
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'?0 
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.0

Just reread 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?
0 
doningtonphil said:Just reread 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?
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.0 
doningtonphil said:Just reread 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.
2 
Just use Calendar??
No longer a spouse, or trailing, but MSE won't allow me to change my username...0 
trailingspouse said:Just use Calendar??0

molerat said:doningtonphil said:Just reread 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.0
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