Im not sure if you can. I had a look through the fuctions and cant see one that relates to last day of month.
Im probably wrong and it probably can be done, in the meantime, take a look at these formulas I wrote (only this morning ironically). They may be able to help you with what you need.
@leapyear formula:
IF year(currentdate)=2012 THEN 29 ELSE
IF year(currentdate)=2016 THEN 29 ELSE
IF year(currentdate)=2020 THEN 29 ELSE
IF year(currentdate)=2024 THEN 29 ELSE
IF year(currentdate)=2028 THEN 29 ELSE 28
Days in month / Last day of month formula:
IF month(currentdate)=1 THEN 31 ELSE
IF month(currentdate)=3 THEN 31 ELSE
IF month(currentdate)=4 THEN 30 ELSE
IF month(currentdate)=5 THEN 31 ELSE
IF month(currentdate)=6 THEN 30 ELSE
IF month(currentdate)=7 THEN 31 ELSE
IF month(currentdate)=8 THEN 31 ELSE
IF month(currentdate)=9 THEN 30 ELSE
IF month(currentdate)=10 THEN 31 ELSE
IF month(currentdate)=11 THEN 30 ELSE
IF month(currentdate)=12 THEN 31
If you wanted to show the date of the last day of the month, then simply amend your formula to read:
IF month(currentdate)=1 THEN "31/01/"&year(currentdate) ELSE
IF month(currentdate)=3 THEN "31/03/"&year(currentdate) ELSE
IF month(currentdate)=4 THEN "30/04/"&year(currentdate) ELSE
IF month(currentdate)=5 THEN "31/05/"&year(currentdate) ELSE
IF month(currentdate)=6 THEN "30/06/"&year(currentdate) ELSE
IF month(currentdate)=7 THEN "31/07/"&year(currentdate) ELSE
IF month(currentdate)=8 THEN "31/08/"&year(currentdate) ELSE
IF month(currentdate)=9 THEN "30/09/"&year(currentdate) ELSE
IF month(currentdate)=10 THEN "31/10/"&year(currentdate) ELSE
IF month(currentdate)=11 THEN "30/11/"&year(currentdate) ELSE
IF month(currentdate)=12 THEN "31/12/"&year(currentdate)
@leapyear formula:
IF year(currentdate)=2012 THEN "29/02/2012" ELSE
IF year(currentdate)=2016 THEN "29/02/2016" ELSE
IF year(currentdate)=2020 THEN "29/02/2020" ELSE
IF year(currentdate)=2024 THEN "29/02/2024" ELSE
IF year(currentdate)=2028 THEN "29/02/2028" ELSE
"28/02/"&year(currentdate)
I also worked out the UK working days for the next 4 years:
//2011 working days
IF year(currentdate)=2011 and (month(CurrentDate)=1) then 20 else
IF year(currentdate)=2011 and (month(CurrentDate)=2) then 20 else
IF year(currentdate)=2011 and (month(CurrentDate)=3) then 23 else
IF year(currentdate)=2011 and (month(CurrentDate)=4) then 18 else
IF year(currentdate)=2011 and (month(CurrentDate)=5) then 20 else
IF year(currentdate)=2011 and (month(CurrentDate)=6) then 22 else
IF year(currentdate)=2011 and (month(CurrentDate)=7) then 21 else
IF year(currentdate)=2011 and (month(CurrentDate)=8) then 22 else
IF year(currentdate)=2011 and (month(CurrentDate)=9) then 22 else
IF year(currentdate)=2011 and (month(CurrentDate)=10) then 21 else
IF year(currentdate)=2011 and (month(CurrentDate)=11) then 22 else
IF year(currentdate)=2011 and (month(CurrentDate)=12) then 20 else
//2012 working days
IF year(currentdate)=2012 and (month(CurrentDate)=1) then 21 else
IF year(currentdate)=2012 and (month(CurrentDate)=2) then 21 else
IF year(currentdate)=2012 and (month(CurrentDate)=3) then 22 else
IF year(currentdate)=2012 and (month(CurrentDate)=4) then 18 else
IF year(currentdate)=2012 and (month(CurrentDate)=5) then 22 else
IF year(currentdate)=2012 and (month(CurrentDate)=6) then 19 else
IF year(currentdate)=2012 and (month(CurrentDate)=7) then 22 else
IF year(currentdate)=2012 and (month(CurrentDate)=8) then 22 else
IF year(currentdate)=2012 and (month(CurrentDate)=9) then 20 else
IF year(currentdate)=2012 and (month(CurrentDate)=10) then 23 else
IF year(currentdate)=2012 and (month(CurrentDate)=11) then 22 else
IF year(currentdate)=2012 and (month(CurrentDate)=12) then 19 else
//2013 working days
IF year(currentdate)=2013 and (month(CurrentDate)=1) then 22 else
IF year(currentdate)=2013 and (month(CurrentDate)=2) then 20 else
IF year(currentdate)=2013 and (month(CurrentDate)=3) then 20 else
IF year(currentdate)=2013 and (month(CurrentDate)=4) then 21 else
IF year(currentdate)=2013 and (month(CurrentDate)=5) then 21 else
IF year(currentdate)=2013 and (month(CurrentDate)=6) then 20 else
IF year(currentdate)=2013 and (month(CurrentDate)=7) then 23 else
IF year(currentdate)=2013 and (month(CurrentDate)=8) then 21 else
IF year(currentdate)=2013 and (month(CurrentDate)=9) then 21 else
IF year(currentdate)=2013 and (month(CurrentDate)=10) then 23 else
IF year(currentdate)=2013 and (month(CurrentDate)=11) then 21 else
IF year(currentdate)=2013 and (month(CurrentDate)=12) then 20 else
//2014 working days
IF year(currentdate)=2014 and (month(CurrentDate)=1) then 22 else
IF year(currentdate)=2014 and (month(CurrentDate)=2) then 20 else
IF year(currentdate)=2014 and (month(CurrentDate)=3) then 21 else
IF year(currentdate)=2014 and (month(CurrentDate)=4) then 20 else
IF year(currentdate)=2014 and (month(CurrentDate)=5) then 20 else
IF year(currentdate)=2014 and (month(CurrentDate)=6) then 21 else
IF year(currentdate)=2014 and (month(CurrentDate)=7) then 23 else
IF year(currentdate)=2014 and (month(CurrentDate)=8) then 20 else
IF year(currentdate)=2014 and (month(CurrentDate)=9) then 22 else
IF year(currentdate)=2014 and (month(CurrentDate)=10) then 23 else
IF year(currentdate)=2014 and (month(CurrentDate)=11) then 20 else
IF year(currentdate)=2014 and (month(CurrentDate)=12) then 21 else
//2015 working days
IF year(currentdate)=2015 and (month(CurrentDate)=1) then 21 else
IF year(currentdate)=2015 and (month(CurrentDate)=2) then 20 else
IF year(currentdate)=2015 and (month(CurrentDate)=3) then 22 else
IF year(currentdate)=2015 and (month(CurrentDate)=4) then 20 else
IF year(currentdate)=2015 and (month(CurrentDate)=5) then 19 else
IF year(currentdate)=2015 and (month(CurrentDate)=6) then 22 else
IF year(currentdate)=2015 and (month(CurrentDate)=7) then 23 else
IF year(currentdate)=2015 and (month(CurrentDate)=8) then 20 else
IF year(currentdate)=2015 and (month(CurrentDate)=9) then 22 else
IF year(currentdate)=2015 and (month(CurrentDate)=10) then 22 else
IF year(currentdate)=2015 and (month(CurrentDate)=11) then 21 else
IF year(currentdate)=2015 and (month(CurrentDate)=12) then 21
Hope you, or anyone find these useful.
Regards,
mike
Edited by Robotacha - 05 Oct 2011 at 12:50am