Skip to main content

WEEKDAY Formula Odd Behavior

  • November 6, 2020
  • 2 replies
  • 49 views
L

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

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • November 6, 2020

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"
)
L
  • Leslie_McKenzie
  • Author
  • New Participant
  • November 6, 2020

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

Terms & ConditionsAccessibility statement