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

Sample Date.xlsx

paulfound

 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 🙂

Week and Month Number based on a particular date.yxmd