date: how to get the last day of december last year?

[choubix]

hello,

I think everything is in the title
I have a start date: eomonth(today())
now I'd need to return the last day (preferably not a week end) of the month of december last year.

I believe it is quite easy when you know how to pile up the date functions but I have difficulties coming up with a solution for this one.

can someone suggest me how to ahndle this please?

thanks!

 

[mvptomlinson]

You might try:

=DATE(YEAR(TODAY()),1,0)

 

[mvptomlinson]

To get the last weekday of the year (Mon-Fri), perhaps:

=DATE(YEAR(TODAY()),1,LOOKUP(WEEKDAY(DATE(YEAR(TODAY()),1,0)),{1,2,3,4,5,6,7},{-2,0,0,0,0,0,-1}))

This assumes Sunday=1, Monday=2...Saturday=7.

 

[choubix]

thanks! used the first formula and it works fine. jsut need to adjust with a -1 or -2 to get the date I need to use.

 

[mvptomlinson]

You're welcome. The second formula is written so that you don't have to manually adjust for -1 or -2, it finds the last day of the year, determines the day of the week and then subtracts 0, 1 or 2 automatically. Mon-Fri = 0, Sat = -1, Sun = -2.

Up to you which suits you best, though. Cheers.

 

[barry houdini]

For last WEEKDAY of last year try using WORKDAY, i.e.

=WORKDAY(DATE(YEAR(TODAY()),1,1),-1)