Skip to main content

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

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"

)

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

Reply


Terms & Conditions