# 'If' Formula - equaling Date

Topic Labels: Formulas
1656 8
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hello,

I am trying to use an IF formula to equal a date but can’t find how to do it.

I want to create IF({WEEK} = “Wk 1”, “15/01/22”

example.
wk 1 = 15/01/22
wk 2 = 23/02/22
Wk 3 = 30/02/22

Does anyone know how to do this?

Thanks

8 Replies 8
10 - Mercury

Can you maybe explain more precisely what you’re trying to achieve. Otherwise the formula you stated looks good already.

13 - Mars

You can do a nested IF formula, although this will get complicated for lots of weeks/dates. A better option is SWITCH:

But even then, this doesn’t feel like it is particularly future-proof - you might have to keep adding weeks/dates as time progresses. If you’ve got some screenshots and/or description of what you are trying to get to, then someone might be able to offer a better solution.

5 - Automation Enthusiast

The formula I am using is correct but I need it to come out as a ‘date’ format but not sure if you can do that within a formula. I run another formula off this cell in a date form, does that make sense?

10 - Mercury

Not sure I understand 100% now, but you can use DATETIME_FORMAT() [Supported format specifiers for DATETIME_FORMAT – Airtable Support] to format the date within your formula.

So as an example:

DATETIME_FORMAT(IF({WEEK} = “Wk 1”, “15/01/22”), “yyyy-mm-dd”)

Please check the reference for all the potential formatting options.

Does that make sense?

Unfortunately that’s not a valid use of the `DATETIME_FORMAT()` function. The return of the `IF()` function in the first argument is a string—“15/01/22”—but `DATETIME_FORMAT()` requires a datetime (a data item representing a date and time) as the first argument. There isn’t a way to represent a datetime as text. Datetimes are created by date fields, or by formulas that return a datetime using certain datetime-related functions. Also, `DATETIME_FORMAT()` returns a string, and @Holly_Matterson indicated that an actual date (i.e. a datetime) was needed.

@Holly_Matterson Your example looks a little off. You list 15/01/22 as the first week, but 23/02/22 isn’t a week after that. Are these not literal consecutive weeks that you need for the output? (Also, 30/02/22 is invalid, as there aren’t 30 days in February).

Assuming for a moment that 15/01/22 (i.e. January 15, 2022) is what you want for the first week (Wk 1), and each subsequent week should be exactly 7 days after the previous one, this formula will work:

``````IF(Week, DATEADD(DATETIME_PARSE("15/01/22", "DD/MM/YY"), VALUE(Week) - 1, "weeks"))
``````

10 - Mercury

I learned something, thank you!

5 - Automation Enthusiast

Sorry, yes! The dates I put there are just examples, not real dates.

I am following the financial calendar
wk 1 - 28/06/21 - July
wk 2 - 5/07/21 - July
and so on

I basically just want to be able to select week and then it automatically enters ‘upload month’ as the month, the upload week as the week of the month and the “upload date” comes up as date but I use the date in other columns for formulas, that’s why it needs to be a ‘date’

I tried the formula above but it came out with a weird random date, so I still can’t figure it out :frowning:

You would need to change the date parsed by the `DATETIME_PARSE()` function to “28/06/21” in order to match the financial schedule that you listed. Here’s the full formula with that change:

``````IF(Week, DATEADD(DATETIME_PARSE("28/06/21", "DD/MM/YY"), VALUE(Week) - 1, "weeks"))
``````

Here are the other formulas that I’m using:

``````IF({Upload Date}, WEEKNUM({Upload Date}))
``````IF({Upload Date}, DATETIME_FORMAT({Upload Date}, "MMMM"))