Jump to content
Sign in to follow this  
Bekah

Deriving Quarter from MMYYYY

Recommended Posts

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). confused.gif 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. wink.gif

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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. crazy.gif

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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!! grin.gif

Share this post


Link to post
Share on other sites

You can also use

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

Share this post


Link to post
Share on other sites

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. cool.gif

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.