Getting a date's week number within a month (not a year)

1

The recent date function additions are awesome, but I’ve run into a new scenario that can’t be solved with them alone I don’t think.

I’m trying to get the week number of a month, not of the whole year. So the output would need to be either 1, 2, 3, 4 or sometimes, but not often 5.

For example, if the input was “Jan 20, 2025”, I would want the output to be “4”

Jan 1-4 - Week 1
Jan 5-11 - Week 2
Jan 12-18 - Week 3
Jan 19-25 - Week 4
Jan 26-31 - Week 5

I have made the following basic formula, but it ultimately fails because it doesn’t factor in what day of the week the first day lands on.

If(
  Day([Service Start]) <= 7,
  1,
  If(
    Day([Service Start]) <= 14,
    2,
    If(
      Day([Service Start]) <= 21,
      3,
      If(Day([Service Start]) <= 28, 4, 5)
    )
  )
)

@Chr1sG Could I humbly request your date formula wizardry once again? :slight_smile:

1 Like
2

@Chr1sG for complete context, I’m trying to automate the creation of a new entity that’s populated with a date that is a month after the previous entity. However the date needs to be set to specific day of the week each month, like “the first Monday” or “the third Wednesday”, not the exact day number. I was thinking week number in a month was important, but maybe there’s a better way?

Basically, on the 1st of every month, if a service.state = active and service.frequency = monthly, create a new “appointment” entity linked to that service and set appointment.date to the service.appointment.sort(date).last.date’s day of the week and occurrence position.

So if service.appointment.sort(date).last.date = Jan 22, 2025, which is the 4th Wednesday of that month, the new appointment’s date would be set to Feb 26, 2025. And for the next appointment after that it would be Mar 26, 2025, then April 23, 2025, etc, etc.

3

If you want to find the equivalent day in the following month, try this formula:

DateField +
  Days(
    If(
      IsoWeekday(
        Date(Year(DateField + Months(1)), Month(DateField + Months(1)), 1)
      ) -
        IsoWeekday(DateField) +
        If(
          IsoWeekday(
            Date(Year(DateField + Months(1)), Month(DateField + Months(1)), 1)
          ) -
            IsoWeekday(DateField) <
            0,
          7,
          0
        ) >
        0 and
        IsoWeekday(
          Date(Year(DateField + Months(1)), Month(DateField + Months(1)), 1)
        ) -
          IsoWeekday(DateField) +
          If(
            IsoWeekday(
              Date(Year(DateField + Months(1)), Month(DateField + Months(1)), 1)
            ) -
              IsoWeekday(DateField) <
              0,
            7,
            0
          ) <=
          Day(
            Date(Year(DateField + Months(1)), Month(DateField + Months(1)), 1) -
              Days(1)
          ) -
            28,
      35,
      28
    )
  )

If DateField is Jan 22 2025, then the result will be Feb 26 2025.
If DateField is Feb 26 2025, then the result will be Mar 26 2025.
And so on.

The only problem is that it breaks down if the following month does not have enough days of the day-of-the-week in question. For example, January 29 2025 is the 5th Wednesday in the month, but there aren’t 5 Wednesdays in February, so there is no correct answer (and the formula in this case will roll over to the next month).

It doesn’t answer your original request, but I hope it addresses the underlying need.