Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

WEEKDAY Formula Odd Behavior

Topic Labels: Formulas
312 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Maybe I have got my formula wrong, but I checked and checked it. What I am trying to get down to is a count of how many specific weekdays in a month.

So for a schedule date of 11/05/2020, the formula would read “1st Thursday of the month” (11/12/2020 would read “2nd Thursday of the month” and so on)

I got this far, and it gives me the first day of the month (so I can build a calculation), which returns 11/01/2020:
IF({Scheduled Start (CST)}="","",MONTH({Scheduled Start (CST)})&"/01/"&YEAR({Scheduled Start (CST)}))

However, when I add the WEEKDAY wrapper, it just returns a flat blank cell.
IF({Scheduled Start (CST)}="","",WEEKDAY(MONTH({Scheduled Start (CST)})&"/01/"&YEAR({Scheduled Start (CST)})))

For the record, I did already try wrapping the date in a DATETIME_FORMAT which also gave a flat blank cell. Some issue with the way I am using WEEKDAY. Any ideas???

2 Replies 2

Welcome to the Airtable community!

Writing complex formulas can be a bit of an art.
Your formula returns a string that looks like a date, not a date object. However the WEEKDAY function needs a date object, not a string.

I suggest a different approach using DATETIME_FORMAT to get the day of the week, and using the DAY to find out which week it is.

IF({date},
  IF( DAY({date}) <= 7, "1st ",
  IF( DAY({date}) <= 14, "2nd ",
  IF( DAY({date}) <= 21, "3rd ",
  IF( DAY({date}) <= 28, "4th ", "5th "
  )))) & 
  DATETIME_FORMAT({date}, 'dddd') & " of the month"
)

Thank you! This did work and I will mark it as the solution, however do you have any ideas why my formula returned a blank value instead of a #ERROR? As I mentioned, I did try wrapping the date object in DATETIME_FORMAT, so the formula looked like this:

IF({Scheduled Start (CST)}="","",WEEKDAY(DATETIME_FORMAT((MONTH({Scheduled Start (CST)})&"/01/"&YEAR({Scheduled Start (CST)}))))

Thank you for your help, it worked great. Just wondering what causes this issue because the logic looks fine to me? Especially a blank cell instead of throwing an error is very odd