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

Previous month

balaji3081

balaji3081

Member
Hi, I have a period column which has 12 months rolling period in yyyymm format (201210 - 201309), I need two columns YTD and MTD, based on what is there on the period column teh YTD and MTD should auto calculate , Don't want to use any reference cell apart from what is there in the period column...


By the way - I get this data from Access, any the idea was to do it in access it self, tried with a access forum but no luck , below is the link



http://www.accessforums.net/access/get-time-frame-query-using-period-column-38974.html
 
I'm not quite sure, but based on the title I made the following formula:
Code: 
=TEXT(DATE(LEFT(A1,4),RIGHT(A1,2)-1,1),"yyyymm")
A1 is the "month rolling period" you mentioned
 
My fault with the title, your formula gives me the previous month, but i need it to calculate if its MTD or YTD, based on the max month in the period column....
 
Hi Balaji,
You confuse me.

I had to search wikipedia for "MTD":
"Month-to-date (MTD) is a period starting at the beginning of the current month and ending at the current date"
-- So for today this would be 1 Okt 2013 - 31 Okt 2013


-- For tomorrow this would be 1 Nov 2013 - 1 Nov 2013

  • So do you want a TRUE or FALSE result based on the "rolling period"-column?
  • Or do you want to return the first day of the month and/or last day of the month? Either for the current date or from the "rolling perdiod"-column?
  • How do you see the use of "max month" in the period column? Shouldn't this always be 12 (aka December)?
I think it would be useful if you give an example and/or an sample file.
 
Hi guys, sorry for the delayed reply, and Xiq sorry for the "MTD" and "YTD" terms, think I was able to solve it , I was looking for getting the MTD and YTD text if it was current month and Current year respectively. I changed it in access and have put in formulas

MTD: IIf([PERIOD]=[Enter End Period],"MTD","")

YTD: IIf(Left([PERIOD],4)=Left([Enter End Period],4),"YTD","")

The "[Enter End Period]" parameter is picked from Excel.

Thanks,
Balaji
 
You must log in or register to reply here.
Back
Top