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
Solved! Go to Solution.
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")
Actually ignore that, thats not what your looking for
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 🙂
Thank you so much. This really worked!
What if you need the date difference in months and weeks