Date lookup to determine fiscal month

[ajv]

I have a call center log with dates. I would like to determine the fiscal month ( which is different from the calendar month ) by checking if the date falls between start of fiscal month date and end of fiscal month date.

Any suggestions?

Thanks.

ajv

 

[MichaelRed]

If the date for the changeover is a constant, you could just do the data math. Otherwise, a table appears to be an acceptable mechanisim.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Louiegee]

For us our fiscal year starts in July, so to find out the fiscal month you just need to subtract 6 from the month (which makes July the first month). If you wind up zero or below you add 12. January (1)-6 = -5 +12 = 7. January is our 7th fiscal month.

That's how to calculate the fiscal month.

 

[Louiegee]

So then I figured, that that might not be all that clear, soooo . . .
you can put this expression in a query:

datepart(&quot;m&quot;,[somedate])-6 + iif(datepart(&quot;m&quot;,[somedate])-6<1,12,0)

substitute your date field's name for the word 'somedate' in both places. And adjust the 6 in both places to correct the fiscal year offset. It should be the number of the month your fiscal year starts less one.

LouieGee