'Simple' Excel spreadsheet - date next due

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

Comments

  • molerat
    molerat Posts: 31,851 Forumite
    Name Dropper Photogenic First Post First Anniversary
    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))))

  • 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
    molerat Posts: 31,851 Forumite
    Name Dropper Photogenic First Post First Anniversary
    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. 
  • 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
    Mistral001 Posts: 5,349 Forumite
    First Anniversary Name Dropper First Post I've been Money Tipped!
    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.
  • molerat
    molerat Posts: 31,851 Forumite
    Name Dropper Photogenic First Post First Anniversary
    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.

  • Just use Calendar??
    No longer a spouse, or trailing, but MSE won't allow me to change my username...
  • Just use Calendar??
    How do I do that exactly?
  • 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
Meet your Ambassadors

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