• 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.

YTD Calculation Begining of Year

P

prasaddn

Active Member
Hi,


Honestly with this site, I am gaining knowledge, but have to agree I have become lazy. My logical thinking capacity has reduced, as I have started resorted to short cut solutions from experts here :(


Well, here comes my another problem.


I am working on YTD calculation for attrition, for which I would need number of days between start of the financial year and end of this month.


eg: for today, 7th Feb 2012, begining of the year is 01-Apr-2011 and will continue to be same until 31 mar 2012. From Apr 2012 to Mar 2013 01-Apr-2012 will be the begining of the year.


So I decided to define "BOY" (Begining of Year) using the formula like this:

=DATE(YEAR(TODAY())-IF(MONTH(TODAY())<4,1,0),4,DAY(1))


I then use EOMonth((today(),0) - BOY to get number of days between start of year and end of this month.


I also verified they are working perfectly correct, but I am not convinced if I am using right methods.


Am I doing anything wrong here, i mean tedious method rather than using any inbuilt functions?


Regards,

Prasad DN

PS: Apologies for the lengthy post :(
 
Prasad,


Use the YEARFRAC function.


It's =YEARFRAC(StateDate,EndDate,Basis). One thing, if you enter the date in the formula, then you have to use the DATE function. Best thing to do is have the YEARFRAC formula refer to dates in another cell.
 
Good function I never noticed it, but I am afraid I would not be able to use them for two reasons.


1. It uses difference between two dates and divides by 360 or 365 as choosen, while I need the YTD to have 365/(difference between two dates)


2. The start date which I referred above needs to be dynamic 1 april of financial year.


Regards,

Prasad DN
 
I'm afraid I don't understand your requirements.


1. If you divide 365/EndDate-StartDate), you will end up with a number greater than 1 for a fraction of the year.


For example, if you use today's date (Feb. 07) and Apr. 1, 2011 as the start date, then YEARFRAC returns 0.85. Doing it your way returns 1.16.


2. You should be able to set the correct FY start date in either a named range or in a direct cell reference.


If you have to do attrition reports across multiple FY for 1-year, 2-year and 5-year rates, then just set up each specific FY start date in its own named reference. For example, I have a FY2010Start FY2011Start FY2012Start FY2013Start ranges in almost of all my worksheets.
 
You must log in or register to reply here.
Back
Top