Display first day of each subsequent quarter based on a date input.

[TJIT]

I have a user input field that someone enters a date into. Based on that date, the report should show the first day of the month of the very next quarter, and every subsequent quarter.

For instance, if I put today's date in the user field, 8/6/2013, the report should show 11/1/2013. On 11/2/2013, the field should start showing 2/1/2014, and so on.

Likewise, if I put the date 3/11/2014 into that field then the report would show 5/1/2014.

 

[pmax9999]

Try:

DateSerial(Year(CurrentDate),Month(CurrentDate)+3,1)

Pete

 

[fisheromacse]

I am going to guess that the dates below are the quarters, if not you will need to adjust the formula.
11/1/2013
2/1/2014
5/1/2014
8/1/2014
11/1/2014

I do not have crystal in front of me and have not yet completed my first cup of coffee so i apologize in advance for any mistakes, misunderstandings and errors.

the formula below should examine the input date's month to return the next quarters initial month.
it then examines the date's month and for 11 & 12 advances the year by one.
there is probably a simpler way but.....

//{@FutureDate}
numbervar m1;
numbervar y1;

IF Month({InputDate}) in [2,3,4] then m1:=5
else if Month({InputDate}) in [5,6,7] then m1:=8
else if Month({InputDate}) in [8,9,10] then m1:=11
else if Month({InputDate}) in [1,11,12] then m1:=2;

IF Month({InputDate}) in [11,12] then y1 := year({InputDate})+1
else y1:=Year({InputDate});

m1 & "/01/" & y1

 

[TJIT]

This works perfectly! I just needed to change "(CurrentDate)" to coincide with my field and was done.

Thank you so much, Pete.

Ted

 

[TJIT]

Actually I jumped the gun on my last post, Pete. Some of our starting dates are from 2001 and the formula:

DateSerial(Year(CurrentDate),Month(CurrentDate)+3,1)

Works only through the following year but then stops working; so that an input date of 11-2-2001 shows 2-1-2002 on the report I run today. It should show 11-1-2013 as of today (8-7-2013).

Fisheromacse, I will give your formula a try and report back.

 

[TJIT]

Fisheromacse,

I'm getting the same results using your formula as I was Pete's. I am pasting the formula below showing my input date (which is a formula called @Start_date):

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
numbervar m1;
numbervar y1;

IF Month({@Start_date}) in [2,3,4] then m1:=5
else if Month({@Start_date}) in [5,6,7] then m1:=8
else if Month({@Start_date}) in [8,9,10] then m1:=11
else if Month({@Start_date}) in [1,11,12] then m1:=2;

IF Month({@Start_date}) in [11,12] then y1 := year({@Start_date})+1
else y1:=Year({@Start_date});

m1 & "/01/" & y1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Seems as if the line,

IF Month({@Start_date}) in [11,12] then y1 := year({@Start_date})+1

is not incrementing as it should. Is that what you think?

 

[fisheromacse]

can you provide some sample data and also a sample of the results you want from the data.
i think it would help understand what is needed to get it right.

 

[TJIT]

Let me know if this helps.

This first picture shows the front end user field asking for the "Original Review Date."

http://i.imgur.com/ORCHPYv.jpg

Once this date has been established, the report should show the first day of the next quarter...of this current year. The date in my screenshot shows 11/1/2011 and, since today is 8-7-2013, the "Review Date" on the report should show 11-1-2013. Currently, it shows 2-1-12:

http://i.imgur.com/3HkcnBQ.jpg

The rationale behind all this is that we need to review our current vendor list every quarter so the report should automatically calculate those dates. It really only applies to new vendors since, after a year, everyone will be synchronized and reviewed at the same time.

 

[fisheromacse]

ok, i am more confused now, so please bear with me.

if you have a date (as in your first image) of 4/3/2003 do you want to return the value 5/1/2013 ?
or 6/5/2010 returns 8/1/2013 ?
or 12/3/2011 returns 2/1/2014 ?
or do all of them return 11/1/2013 ?
or ???

 

[pmax9999]

You will get very different results using fisheromacse's code than mine. My interpretation is that you wanted the quarters based on the Starting Date. Fisheromacse's code looks to be setting fixed quarters, regardless of the Starting Date.

So assuming I interpreted your requirements correctly, and using your {@Start_date} formula as the starting point, my suggested code should look like this:

DateSerial(Year({@Start_date}),Month({@Start_date})+3,1)

If the quarterly dates are fixed, fisheromacse's code should work. Either way, if the codes are providing the incorrect years, please post the code for your {@Start_date} formula - the issue must be in there somewhere.

Pete

 

[pmax9999]

Was thinking about this some more and I realised it was likely I had misundertsood what you were trying to do. My last post provided the date of the first Quarterly Review Date, but on re-reading your original post I think what you are looking for is the next Quarterly Review Date.

This formula should do what you want (unless I still don't understand which is entirely possible of course):

DateAdd("q",DateDiff("q", DateSerial(Year({@Start_Date}),Month({@Start_Date})+3,1), CurrentDate) + 1, DateSerial(Year({@Start_Date}),Month({@Start_Date})+3,1))

For my testing I used a {@Start_Date} of 6 Aug 2011. When run today it returns 1 Nov 2013. On 2 Nov 2013 it returns 1 Feb 2014. Is that what you are trying to achieve?

Hope this helps

Pete

 

[TJIT]

I think we're onto something here, Pete. I am getting a couple rogue dates showing up which, if figured out, will call this one done. I am attaching a screenshot of the report with the two fields highlighted:

http://i.imgur.com/5TIZfzV.jpg

But otherwise, I think we're barking up the right tree.

 

[pmax9999]

In looking a the first example in your link, a First Review Date of 1 April would mean subsequent reviews on 1 Jul, 1 Oct and 1 Jan yearly. Wouldn't the next review then be due 1 Oct 2013?

Anyway, the second highlighted in your link was definitely wrong - something to do with the way Crystal counts Quarters in the DateDiff function, so I amended the formula to calcuate quarters manually. Try:

DateAdd("q",Truncate((DateDiff('M', DateSerial(Year({@Start_Date}),Month({@Start_Date})+3,1), CurrentDate))/3) + 1, DateSerial(Year({@Start_Date}),Month({@Start_Date})+3,1))

Pete

 

[fisheromacse]

Pete,
Very nice!
I am glad you understood better than I and were able to help!

 

[TJIT]

Pete,

The "first review date" is when the vendor came online with the company. Once that first review is done, their schedule synchronizes with all other existing vendors, which is currently 11-1-13.

I added your new formula and got additional dates:

http://i.imgur.com/QAd19Sp.jpg

I have an idea. Would it simplify things if we just remove the @start_date from the formula and just have it print 11-1-xx, 2-1-xx, 5-1-xx, 8-1-xx (xx coinciding with the appropriate year) depending on currentdate? Then I can just pull straight from the user field for the "Initial Review Date.

 

[pmax9999]

TJIT

I thought I understood what you were trying to do but apparently I dont.

Looking at the 2 examples you have highighted, my logic was as follows:

1st Review 5 Jan:
Subsequent reviews would be due 1 Apr, 1 Jul, 1 Oct and 1 Jan yearly. As of Aug 2013, next review due 1 Oct 2013 which is what the report says.

1st Review 8 Jun:
Subsequent reviews would be due 1 Sep, 1 Dec, 1 Mar and 1 Jun yearly. As of Aug 2013, next review due 1 Sep 2013 which is what the report says.


I have tried to assist based on what I understood to be the requirement. Happy to leave it for someone else to assist if I have missed the point.

Pete.