Skip to main content

Extend a WEEKNUM formula

  • December 16, 2024
  • 2 replies
  • 20 views
J
Forum|alt.badge.img+3

Hi,

I was trying to extend the below formula to give me records due 2 weeks from now so I modified this:

IF(WEEKNUM({Flight Start},"Monday")=WEEKNUM(TODAY(),"Monday"),"Current Week",
IF(WEEKNUM({Flight Start},"Monday")=WEEKNUM(TODAY(),"Monday")+1,"Next Week"))

To this:

IF(WEEKNUM({Flight Start},"Monday")=WEEKNUM(TODAY(),"Monday"),"Current Week",
IF(WEEKNUM({Flight Start},"Monday")=WEEKNUM(TODAY(),"Monday")+1,"Next Week",
IF(WEEKNUM({Flight Start},"Monday")=WEEKNUM(TODAY(),"Monday")+2,"Two Weeks")))
 
The formula was accepted but it is not giving me any results. Does someone know why?
Do I need to trigger the formula?
 

2 replies

P
Forum|alt.badge.img+9
  • poohyeou
  • New Participant
  • December 17, 2024

Since a year consists of a total of 52 weeks, using WEEKNUM can cause an issue where the last week of December, which includes January, is reset to 1, leading to value errors.

To address this, the formula may need to be modified in a somewhat complex way, or if not using Monday as the reference day, you can try using the following simple code:

IF(
{Flight Start} < TODAY(),
"Past",
IF(
DATETIME_DIFF({Flight Start}, TODAY(), 'days') <= 7,
"Next Week",
IF(
DATETIME_DIFF({Flight Start}, TODAY(), 'days') <= 14,
"Two Weeks",
"Future"
)
)
)
Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • December 17, 2024

Hi,
I tried your formula, it works for Current and Next Week
Doesn't work for 2 weeks, because of year ending and dates (Dec 30-31)  counted as 1st Week of 2025

To avoid overcomplex formula with year end checking ,  I would fix year cycle in a following way:

SWITCH( WEEKNUM({Flight Start},"Monday")-WEEKNUM(TODAY(),"Monday")+ 52*(WEEKNUM({Flight Start},"Monday")-WEEKNUM(TODAY(),"Monday")<-49), 0,'Curent week', 1,'Next week', 2,'2 weeks')

 

Terms & ConditionsAccessibility statement