'Simple' Excel spreadsheet - date next due

doningtonphil

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

tizerbelle

https://support.microsoft.com/en-us/office/add-or-subtract-dates-b83768f5-f695-4311-98b1-757345f7e926

molerat

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))))

doningtonphil

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'?

molerat

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. 

doningtonphil

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?

Mistral001

doningtonphil 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?

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.

molerat

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

trailingspouse

Just use Calendar??

doningtonphil

trailingspouse said:

Just use Calendar??

How do I do that exactly?

doningtonphil

molerat said:

doningtonphil 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