• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date formula issue not displaying correct numbers in correct "field box"

chaosincarnate

New Member
I am having an issue getting a date formula to correctly work.

Starters - some information is in "Company B" for reference.....

I created this a few years ago to track mandatory Urinalyisis testing. In Tab "Company B" Colums F-H does a quick look up to match what is entered in Column B(type of person being tested by their position). Totals it up in row 181-185...works fine so far no issues that I have noticed...

My issue seems to be column J. It should give a correct date for the Quarters...Now understand 1 October is 1st quarter for us.. 1 January starts 2nd Quarter..etc...

Colum J should give a result in J2 as 1 Oct 14 but instead its 1 Oct 13. I played with the formula and can force it show what I want but as 2015 comes around it will not be accurate any more. Similar issues in J3-J5.

Once everything is computed it should place the corect numbers in tab "Rollup" in the correct Quarters...however its not. Placing 1st quarters in 4th Quarters. A simple fix is instead of having a formula I can just change the date every year(J2 = 10/1/2014, J3 = 1/1/2015, etc..) but i dont want to. I want it to update itself with out manually entering a new date yearly. It worked great in the past but recently it was noticed it was placing things in the wrong quarters in the rollup tab. All "Company tabs" are the same just linked to their respective company on the rollup tab.


Sorry if this sounds confusing...in my mind it sounds logical...

I'm illiterate in VB, macros, and alot of the more detailed formulas of EXCEL.
 

Attachments

  • UA Tracker FY 15.xls
    448 KB · Views: 6
=CHOOSE(MONTH(D2),2,2,2,3,3,3,4,4,4,1,1,1) - Will get you your custom quarters, I believe, where the value in D2 is a date.
Use Excel's Table feature to hold your data and the rollup should come from a Pivot on your table

When I get home I'll take a further look at what you're trying to get to ...
 
Hi ,

I think it is simpler than you think it is.

The logic should be as follows :

If the date derived by using the year for the date in J11 , a month value of 10 ( since your year always starts from October 1 ) , and a day value of 1 is greater than the date in J11 , it means we should go back one year else we are in the current year. So this should work for the first quarter starting date :

=IF(DATE(YEAR(J11),10,1)>J11,DATE(YEAR(J11)-1,10,1),DATE(YEAR(J11),10,1))

All other quarter starting dates are derived from the first quarter using the EDATE function , by :

=EDATE(J2,3) , =EDATE(J3,3) and =EDATE(J4,3)

Narayan
 
Thanks alot Narayan worked perfectly

David....I entered yours into J2 and it gave me 1 jan 00 as a "DATE" for the quarters (D2 was 1 Nov 14). But I probably misunderstood what you said... I'll continue playing with though. I like to have multiple ways of doing things.
 
Back
Top