Show the month on a fiscal week

[Dayna]

Hello all,

I am hoping that you can help me, I am currently working with some data that has weeks 1-52 but it runs from April-March... I have the weeks and year, but I would like to show what month it belongs to. How is best to do this?

You're help is most appreciated!!!

Kind Regards,
Dayna

[#2]

you have a lot of options........sometimes i use a excel files with From-To and use a both calendar in application.....

or depends your problem you can do that in script by IF or Case....

[tabletuner]

Hi Danya,

Maybe this example is helpful.

Table:
LOAD
Year,
Week,
Month(makeweekdate(Year,Week)) as Month
FROM table.xls

Keep in mind that your listbox will probably display week 53 in a wrong way. If so, dont use the weekno's from the transaction table but create a calendar table which you sort on weekno. Sort the list box on load order.

[Dayna]

Hello Tjeerd,

That's perfect for normal weeks, but unfortunately week one for me starts in April.. Any ideas?

Kind Regards,
Dayna

[tabletuner · Accepted solution]

Yes i have. Since a week has always seven days you can simply add 12 weeks to the calendarweek and create your bookweekno. For example:

month(makeweekdate(Year,Week+12) as bookweekno

Dont worry about i.e. a value of 60 for Week because the makeweekdate function will treat this as a whole year and 8 weeks.

In your case you can sort on bookweekno so forget about my remark about week 53 in my last post.

[Dayna]

Perfect!! Thank you so much!!