Deriving Quarter from MMYYYY

[Bekah]

Hi everyone, sorry to pester. I really try myself first (hours and hours). And i found posts on quarter but they aren't doing it like I am. I have MoYr custom function and Fiscal (year) function. Our fiscal starts 10/1. MoYrFiscal calculation displays like this:

102003

112003

122003

012004

etc. Sorts wonderful. I join these in many ways for portals and splitting up the stuff. i understnd left/right splitting and its easy to work with.. I cant change it now and it actually works well. but now they want the quarter figured also so it displays like:

102003-1

112003-1

122003-1

012004-2

I figured this out but it doesn't display quarter four, it only displays a dash (no number 4). Otherwise it works right. I can't see what I have wrong. Can someone help?

"-" & Case(Left(MoYrFiscal; 1) = 1; 1;

Middle(MoYrFiscal; 2; 2) < 4; 2;

Middle(MoYrFiscal; 2; 2) < 7; 3;

Middle(MoYrFiscal; 2; 2) < 10; 4)

And is there better way even if I get this to work? I always want to do the best way possible. Thank you forum for all the help you've helped me.

[Bekah]

Drat. Just realized this will through off any sorts. I guess I could sort by MoYr only. Cant picture how this would sort right. Maybe it would since its the same length and ends in 1,2,3,4. i sure hope so.

[Bekah]

Ok. I tried changing the calc several times but it just won't work. I keep getting the same results.

"-" & Case(Left(MoYrFiscal; 1) > 0; 1;

Middle(MoYrFiscal; 2; 2) < 4; 2;

Middle(MoYrFiscal; 2; 2) < 7; 3;

Middle(MoYrFiscal; 2; 2) < 10; 4)

I can't figure any othr way to write it. IF won't work and I even tried functions i haven't learned yet. All records should be included so I shouldn't need the "" on the end, right? It didn't seem to make difference anyway. I feel dumb.

[RalphL]

Try the following:

"-" & Case(Left(MoYrFiscal; 1) > 0; 1;

Middle(MoYrFiscal; 2; 2) < 4; 2;

Middle(MoYrFiscal; 2; 2) < 7; 3;

Left(MoYrFiscal; 2) < 10; 4)

[Bekah]

Hi Rakph,

It took a bit to figure why yours worked but yes, I now see it. I had Middle 2 which was looking at the 0 instead of looking at the 10. You are using Left 2 which looks at the 10 as the entire number. Doh! I just spent 16 hours on this. Good grief this can be tiring stuff. I can't thank you enough!!

[-Queue-]

You can also use

MoYrFiscal & "-" & Choose( Div( Left( MoYrFiscal; 2 ) + 2; 3 ) - 1; 2; 3; 4; 1 )

[Bekah]

EEE GADS! I kept looking at Choose because I read it's better but I couldn't see a pattern. I spent an hour list 1-2-3-4 and multiplying *2 and *3 and and dividing (and plus and minus numbers) trying to find a common demoninator. I saw it sorta but it kept breaking because I was put 1 at the first. You put the 1 at the end. I just couldn't see to group them.

I wanted this separate from MoYrFiscal so I can attach or detach depending upon my needs. But I understand exactly what you've given me. Oh, Beatiful. thank you so much. You are brilliant.