Always insert 1st Mon of Jan

[Ltat42a]

I'm looking for some help on a spreadsheet (Excel 2010). I have a cell (C2) that always must be the first Monday of January of the current year. If I open this in 2014, C2 will display the first Monday of Jan in 2014. Is that possible?

Thanx.

[Ace_XL]

A bit long -wounded

Please Login or Register  to view this content.

[Ltat42a]

Hey....that'll work! Thanx man....

[daddylonglegs]

I don't think that formula works if the first Monday of the year is 1st Jan - it returns 8th Jan. Try this version:

=DATE(YEAR(TODAY()),1,8)-WEEKDAY(DATE(YEAR(TODAY()),1,6))

[Ltat42a]

Ok great, Thanx. Question, if I want to work on a future year schedule, how can I use the formula if I base it on a specific year....say 2015?

[daddylonglegs]

If you have the year (e.g. 2015) in A1 you can use this formula for the first Monday of that year

=DATE(A1,1,8)-WEEKDAY(DATE(A1,1,6))

....or just put the year directly in the formula

=DATE(2015,1,8)-WEEKDAY(DATE(2015,1,6))

incidentally - that can work for any day, for Tuesday change the 6 to a 5, for Wed use 4, for Thursday use 3 etc.

[Ltat42a]

Awesome....you're a great help, Thank you.