Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
START_DATE | Anniversary of start date in 2012/3 |
---|---|
15/09/2008 | 15/09/2012 |
26/01/2010 | 26/01/2013 |
13/09/2010 | 13/09/2012 |
31/03/2011 | 31/03/2013 |
17/09/2012 | 17/09/2012 |
24/01/2013 | 24/01/2013 |
Hi all,
I have figures for the start date of employees (column 1 above) and need something in the script to work out the anniversary of their start date in the current year. I have used 2012/3 as an example. I am looking to use the date in the first column to find the date in the second column which I do not have.
Any help would be much appreciated as I have been going round in circles on this one
Many thanks
in the attachment the qvw I used for testing
Your fiscal year 2012/13 starts from which month?
It would start on 01/08 every year
result and expression
makedate(
if(MakeDate(2000, month(START_DATE), Day(START_DATE)) <MakeDate(2000, month(today()), Day(today())),
Year(Today())+1,Year(Today()) )
,month(START_DATE)
,Day(START_DATE)
)
script
t:
load * inline [
START_DATE
15/09/2008
26/01/2010
13/09/2010
31/03/2011
17/09/2012
24/01/2013
];
Hi Lucy,
I'm assuming your 2012/3 is from July2012 till June2013. Therefore I would use.
=makedate(day(START_DATE),num(month(START_DATE)),if(num(month(START_DATE))>6,2012,2013)
Hope this helps
Tobias
what is the logic of 2012 and 2013 in anniversary ?
This appears to be working but how would I get the dates to fall in 2012/3 rather than 2014/5? Would I let Year(Today())-1?
if you want 2 years before current year
makedate(
if(MakeDate(2000, month(START_DATE), Day(START_DATE)) <MakeDate(2000, month(today()), Day(today())),
Year(Today())-1,Year(Today())-2 )
,month(START_DATE)
,Day(START_DATE)
)
For some reason this was only bringing up the year and not the rest of the date but will play around and see if I can make it work
Many thanks
in the attachment the qvw I used for testing