By using this website, you agree to our Terms of Use (click here)
Hey Everyone,
I know the date formulae are a bit rough, but I'm being asked to run 12 Weeks of data (in weekly buckets) Followed by monthly buckets until I've hit Month 6.
Getting 12 Weeks was easy, I ran 12 variables, each with =DateAdd(today(),'d',7), for week 1 and increasing until =DateAdd(today(),'d',84), which is week 12. I'm stuck on how to define the end of the month that Week 12 lands on, further complicated by the fact that Week 12 could BE the last day of the month.
Anyone here a super genius with Date Math? I have a variable $Month3 that comes after $Week12 (I am aware it could technically be month 4, depending on our start date) that I cannot figure out how to design.
The date math doesn't seem to have functions like MonthEnd(), which would make life easy, as I'd do a date diff on $Week12 and MonthEnd($Week12), if it's non-zero, assign MonthEnd() to $Month3 (So I catch the rest of the month), if not Assign the next month's end using DateAdd ($week12 was already at month end, so increment to next month). Am I overlooking something simple? I'm feeling kind of dumb on this one, math is usually my happy place...
As always, thanks in advance for your assistance everyone!
Hey Michael,
Could you do something like this to give you the start of month 3?
Dateadd(today(),'d',84 - (Dayofweek(Dateadd(today(),'d',84))+1))
Or are you looking for calendar months? As in, if 12 weeks got you to mid-december, you need to find December 1 to start the next batch? That would be tricky as I don't believe there is a function for Date(Day,Month,Year) is there?
If the $Week12 date was December 20th, I'd like $Month3 to be December 31st. If $Week12 was December 31st, I'd like $Month3 to be January 31st. I'm trying to either finish the month (no gaps) or jump to the next month (no overlap). The tools we're given are a bit rudimentary and perhaps I just need to make two versions of the report, one running strictly in weeks, and one running in months. That would be simple to implement, but obviously less aligned for the use-case I was given.
Hey Michael,
I haven't solved the problem when it's the last day of the month, but what about this technique?
Using this formula on the Sales Order Date:
=DateAdd(DateAdd([SOOrder.OrderDate],'m',1),'d',-Day([SOOrder.OrderDate]))
The result turns out like this:
