I’d like to be able to automatically add a date to another cell dependent on the date in the first cell
Eg If A1 is before 01/03/2021 then 01/09/2023, otherwise 01/09/2024
=IF(A1>28/02/2021,01/09/2023,01/09/2024)
I have been trying to use the IF function but I keep getting errors - this gives the result is Dec 99 - (this column is formatted as mmm/yy)
Doesn’t matter what date I put in A1 I get the Dec 99 result
What am I doing wrong? Is there other way of doing this?
(ideally I’d like to make the result date is automatically dependent on a range of dates in the first cell - so between 01/03/2020 and 28/02/2021 then 01/09/2023, between 01/03/2021 and 28/02/2022 then 01/09/2024, etc - I can change the dates once I get it working…)
Thanks in advance
IF A1 is smaller than 28 divided by 2 divided by 2021? So, if A1 is smaller than 0,006927264?
Either DATE(2021;2;28) or “2021-2-28” can be used to express a constant date in a formula.
ah - that makes sense. So what I need to do is use the date as a number format - so
=IF(A1<44255,45170,45536)
(Changed to less than - mistake in typing formula in question) and format the answer as date
I’ve just tried that and it works…thanks
not ideal but it helps!
And I’ve played with it a bit more - using the “date” format with " you gave.
Which works but the problem I am finding with this is that it won’t allow me to use the date format ‘Sep 23’ - enters result as 2023-09-01 which is fine but using the numbers it will let me use the format I want.
Anyway thanks again - I knew there would be a way and I have something that works now…
But why not
=IF(A1<DATE(2021;2;28);DATE(2023;9;1); DATE(2024,9;1))
Not everyone can mentally translate the number of the day into a regular date. 
I didn’t ‘mentally’ translate the date - I’m really not that clever! 
I put in the dates and changed the cell format to general number then used that…
I could try the Date() format…might do when I need to update it. (I am the only person who will fiddle with the format etc - as long as the other users can just plug in one date and the right date appear in another box it will be fine…)
Thanks
Formatting means nothing. It makes absolutely no difference at all. All those colors, borders, fonts and number formats do not (and must not) change a single bit of data. The only thing that matters are data and formulas. Formatting is cosmetic. You may cheat yourself about true content when “fiddling around” with formatting.
WEEKDAY(A1) always returns the exact same value, no matter how you format A1 as long as A1 has a number.
NumberFormats.ods (21.1 KB)
Sorry I did mean formulas as well as formatting…basically no one else will do anything to the file but enter the first date (and some other text)
(Date format as MMM YY just makes it easier to spot differences - very occasionally we need to manually change this to another month and this format makes it obvious at a glance)
Thanks again