Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

difference between two dates in weeks

Hi Guys   I need your help.    I have data table with order nr and then 4 dates. - Creation date (this is in date format, but also week and year),  - Req. week (only in week and year format) -...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Greg_Deckler

     

    Almost correct

     

    this is the correct formula that works 

     

    Column =
    VAR __dec31 = DATE([Req. Del. Year],12,31)
    VAR __weeks = [Req. Del. Week]-[Creation Week]
    RETURN
    IF(
    [Creation Week] > [Req. Del. Week],
    WEEKNUM(__dec31) -[Creation Week]+ [Req. Del. Week],
    __weeks
    )

     

    but thank you very much without you I would not able to finish it.. 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

ok thats a good start but how to deal with the fact that each year has a different number of weeks.. 

 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

Try this:

 

Column = 
VAR __dec31 = DATE([Req. Del. Year],12,31)
VAR __weeks = [Creation Week] - [Req. Del. Week]
RETURN  
  IF(
    [Req. Del. Week] > [Creation Week],
    WEEKNUM(__dec31) + __weeks + [Creation Week],
    __weeks
  )
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Greg_Deckler

     

    Almost correct

     

    this is the correct formula that works 

     

    Column =
    VAR __dec31 = DATE([Req. Del. Year],12,31)
    VAR __weeks = [Req. Del. Week]-[Creation Week]
    RETURN
    IF(
    [Creation Week] > [Req. Del. Week],
    WEEKNUM(__dec31) -[Creation Week]+ [Req. Del. Week],
    __weeks
    )

     

    but thank you very much without you I would not able to finish it..