Convert and merge Number and Date

Try

=DATE(C1,1,1)

Format the cell as a date

Thanx.

That works.

I believe Jonmoe's will only work with January first.
Try this, where A3 holds your true DATE and C1 holds the year number:

Hmm, I thought the OP meant that the RESULT would be in A3, and should be January 1st of whatever year is in C1.

I think you're probably right though...

Thanx to both of you for your replies and suggestions.

Here's where I'm going with it:

This is David Seah's Compact Calendar:

http://davidseah.com/blog/comments/compact-calendar/

Credit to him.

I just thought it could do with some useful
mods.

Here's what I sent him (more comments
and translations at the link above)

If it's ok with David, I'll post my tweaked
version here. Otherwise, he may also
post my version and the link to it from his
page.

Still, even without my tweaked version, it
should be easy enough to follow my changes.

I added a few tweaks.

You can change the year but the Holidays
didn't automatically adjust. So, I, with
the help of the folks at MrExcel, figured
out a way to adjust the Holidays automatically
by plugging in the Year from the main Calendar
into the Holiday Dates. This way, when you
change to a new year, the Holidays update to,
and from the same location, so no having to
change each date every time you update.

Here's what I used for the Holiday dates:

=DATE(Calendar!$I$1,1,1)

(change as needed for each Holiday)

One other thing I did was to change the Year
to a drop down list (hidden on the Holidays page
in Columns D-H. It now goes all the way up to
2050. And beyond that, it's a simple matter to
simply update the numbers in the range.

The last thing that I haven't entirely figured out
yet is the whole Holiday Friday, or Monday, or
as in Thanksgiving, the Last Thursday of November.

Using the info from this page:

Calculate Last Sunday and First Sunday in the Month:

http://www.dslimited.biz/excel-zen/...te-last-sunday-and-first-sunday-in-month.html

I used his example to calculate (after setting
up pointer tables, also on the Holidays page
the Last Thursday of November for Thanksgiving.

Here's what I used (via some reference lists as suggested
in the link above) to calculate the Last Thursday in November:

= DATE(Calendar!$I$1, $E$12 + 1, 0) - MOD(WEEKDAY(DATE(Calendar!$I$1, $E$12 + 1, 0)) - 5, 7)

$E$12 is the pointer to the month number: 11

The Days (in this formula "5" = Thursday:

1 - Sun, 2 - Mon, etc

The link explains the process clearly and in such great detail
with numerous examples.

It should also be easy then to calculate any other
1st, 2nd, 3rd, last etc special dates.

Hope this proves useful.

Thanx

You don't really need MOD in the formula for calculating days of the month. Here's a generic version

=DATE(y,m,1+n*7)-WEEKDAY(DATE(y,m,8-d))

where y = year, m =month n = occurrence in month, d = day of week (sun =1 to sat = 7)

Thanksgiving is the 4th Thursday in November, which isn't always the last the last, so translating that formula to calculate Thanksgiving for a variable year in A1

=DATE(A1,11,1+4*7)-WEEKDAY(DATE(A1,11,8-5))

which can be simplified further to

=DATE(A1,11,29)-WEEKDAY(DATE(A1,11,3))

Where you do need the last day of a month, i.e. Memorial Day which is the last Monday in May, you can just calculate the 1st Monday in June and subtract 7 so that would be

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

Cool, Thanx.

This is one of the better calendars I've
seen with the ability to drill down into
the Holidays as we've been discussing:

http://spreadsheetpage.com/index.php/file/yearly_calendar_with_holidays/

but, then, it's John Walkenbach's site,
kind of expect something like this from
him.

The link to my mods to David's Calendar
is now up at David's site (see earlier post
this thread for the link to David's Calendar
(under comments))

or here:

http://home.comcast.net/~mmeroom/CompactCalendar2009us-ms1.xltx

I've included Holiday info from John Walkenbach's
calendar, the info from barry on Date Calculation
and I've updated the Holiday Table to reflect
barry's formula. As well as all associated source
links.

Thanx all.