Help

IF or SWITCH? If the date is 6/29/2023 then it would return "Th" for Thursday

Topic Labels: Formulas
881 4
cancel
Showing results for 
Search instead for 
Did you mean: 
eveningmeadow
4 - Data Explorer
4 - Data Explorer

Hi,

I have a few dates and want to generate the day of the week initial based on the date in a column.

If the date is 6/29/2023, then it would return Th.

I've tried If and Switch, but my formulas are definitely wrong I think because it is related to a date. Ideas?

4 Replies 4

Hi @eveningmeadow 

You can use a formula for this: DATETIME_FORMAT(your date field, 'format specifier') . 

Ok, that is not working for me. 

DATETIME_FORMAT(SWITCH(
 {Start},
   06/30/2023, "F",
   06/29/2023, "Th',
   06/28/2023, 'W',
   06/27/2023, 'T',
   06/28/2023, 'M',
   07/01/2023, 'S'
))

I tried this too 

IF({Start} = DATETIME_FORMAT{06/30/2023}, "F")

To build on @Databaser's suggestion try this: 

DATETIME_FORMAT([your date field], "dd")

This returns the following abbreviations: 

Cherry_0-1677884263693.png

The issue you are encountering is because you need to pass the formula one of the supported specifiers listed on this page: https://support.airtable.com/docs/supported-format-specifiers-for-datetime-format 

If you need just the first letter of the abbreviation except on tuesdays/thursdays you could write: 

 

IF(OR(DATETIME_FORMAT(Date, "dd") = "Tu", DATETIME_FORMAT(Date, "dd") = "Th"), DATETIME_FORMAT(Date, "dd"), LEFT(DATETIME_FORMAT(Date, "dd"), 1))

 

Cherry_1-1677884513597.png

I hope this helps!