Week and Month Number based on a particular date

[Anagha]

Hi,

Could you please help me in finding the week and month number based on a particular date. ie, week and month number of 04/11/2019 is 0, for 31/10/2019 is -1 and -1, for 15/12/2018 week number= -47, month number= -11(based on today's date).

I have tried using the formula  

ToNumber(DateTimeFormat([Usable_Date],"%W")) -
ToNumber(DateTimeFormat(DateTimeToday(),"%W")), but the output seems to be wrong as the date 02/11/2018 shows the week number and month number as 0. I have attached the the dates with week number and month number based on today's date. Kindly help in resolving this,

Thanks,

Anagha

[LordNeilLord]

Hey @Anagha 

If you're looking for an offset from Today() then you would be better using a datetimediff() formula....

DateTimeDiff(DateTimeToday(), [Usable_date], "days")/7

DateTimeDiff(DateTimeToday(), [Usable_date], "months")

[LordNeilLord]

Actually ignore that, thats not what your looking for

[paulfound · Accepted solution]

 Think you'll need to find the first date of the current week and the first date of the usable week then datediff /7.

This will get the first day of this week:

DateTimeAdd(DateTimeNow(),ToNumber(DateTimeFormat(datetimenow(),"%w"))*-1,'days')

This will get the first day of Usable_Date week

DateTimeAdd([Usable_Date],ToNumber(DateTimeFormat([Usable_Date],"%w"))*-1,'days')

this will give you the variance in week numbers:

DateTimeDiff([firstdate_usable_week],[firstdate_current_week],'days')/7 

For months, you'll need to measure from the first of each month.

first of current month:

DateTimeFormat(DateTimeNow(),"%Y-%m-01")

first of usable month

DateTimeFormat([Usable_Date],"%Y-%m-01")

Month diff

DateTimeDiff([first of usable month],[first of current month],'months')

Hope this helps 🙂

[Anagha]

Thank you so much. This really worked!

[amber_data]

What if you need the date difference in months and weeks