'If' Formula - equaling Date

[Holly_Matterson]

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

[Rupert_Hoffsch1]

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

[JonathanBowen]

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

Airtable Support

An alternative to IF statements using SWITCH()

SWITCH() is one of Airtable’s most recent additions to the formula field, and it can be a big time-saver when you’re working with conditional-heavy formulas (e.g. nested IF statements).     To dem...

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.

[Holly_Matterson]

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?

[Rupert_Hoffsch1]

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?

[Justin_Barrett]

Rupert_Hoffschmidt:

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

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

[Rupert_Hoffsch1]

I learned something, thank you!

[Holly_Matterson]

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:

Thanks for your help!

[Justin_Barrett]

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:

Upload Week:

IF({Upload Date}, WEEKNUM({Upload Date}))

Upload Month:

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