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.
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?
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:
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: