Only focus on October because that is only Month you defined.
if date([Date Time])<date(#2016/10/7#) then 9
elseif date([Date Time])<=date(#2016/11/8#) then 10
October_SM_9.2.twbx 127.6 KB
I have a slightly different solution for you, that will be more dynamic than hard-coding the months (and keeps the billing month as a date format)
so first I created a field which returned the Day of the Month
[Day of Month]
and then used this formula to create the Billing Months
[Date Time - Billing Month]
IF ([Day of Month] >=1 AND [Day of Month]<=6) THEN
MAKEDATE(YEAR([Date Time]),MONTH([Date Time])-1,1)
ELSE MAKEDATE(YEAR([Date Time]),MONTH([Date Time]),1)
Now, this looks at the day of the month, and then either creates a date for the 1st of the actual month, or the previous month. Now although all the dates are set to the 1st, as you (from my understanding) will be using this TRUNCed on Month it doesn't really matter.
hope that helps...and Shin hope you don't mind me stepping in here!
Billing Month_SM_9.2_SR.twbx 173.2 KB
Thanks Shin, that helps.
Thanks for the different approach, this solution is awesome but but it always start the same day for each month, which is not as per my requirement.
Cool...glad you got the solution you needed.
We could include the day too, I just used the 1st for ease...I was having a play & here are some other applications of the same general technique (i.e. breaking out the parts of the date, and rebuilding it, but changing the month part in certain circumstances). In these ones, I've recreated the month as strings and then created a bill month calendar (October - Day 1, October - Day 2...etc.).
...might give you some ideas!
Billing Month_SM_9.2_SR.twbx 183.1 KB