'If' Formula - equaling Date

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

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?

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

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

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!