How do I update a conference room sheet to reflect next year's dates?

In the attached XLSX workbook, I have the first  quarter from this year (Jan - Mar 2014) for our conference rooms so we can keep track of reservations. I want to update it so that the first day of 2015 will say Thursday January 1 2015 instead of reflecting the 2014 date. I want to do this for each day in the workbook to reflect the 2015 date. I have 4 workbooks all formatted like this, and will need to change the dates for all 4 workbooks representing each quarter of next year. The formatting is consistent across workbooks and worksheets.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
Unfortunately, the date is split up among several cells, so some combining will be necessary to help create the 2015 date.  It will be simpler if the date resides in one cell so that this will be easier to edit in future years/workbooks.

1) Get the full date for 2015 by adding this formula in column L (starting in cell L1)and copying all the way down to the last used row:
=UPPER(TEXT(IF(K1<>"",DATEVALUE(H1&" "&J1&", "&K1+1),""),"DDDD, MMMM D, YYYY"))

Since your sheets are identically laid out, you can apply this to the first sheet, then select all the formulas in column L and copy them to the other two sheets in column L also.

2) convert all these formulas to values.  Copy all the formulas, and use PasteSpecial - Values to paste them in place (again, in column L).

3) Delete all the values in columns F:K

4) Copy all the values in column L and paste (values only) in column K.  Change the alignment to right-align so that the full date will appear completely in the grey row.

5) Delete column L.

I've applied this to your example workbook for you to see.

David L. HansenCEOCommented:
I just used this:

Open in new window

in the cell where the week-day-name is shown (ie. F1, F42, etc.). And I placed in column L every date for January 2015. So formatting column L as Date then placing 1/1/2015 in L1, and 1/2/2015 in L2, etc. (stretch the pattern down for 31 days) gives me each day of the month. Note: you don't need to use column L, if you'd rather have it hidden somewhere.  Then, that tiny formula above does the trick. You'll just need to copy the formula text and paste it in each F cell and change L1 to L2 then L3 and L4, etc.

Will that work for you?

Also note that once in place, next year you'll just need to change the dates in column L (using the stretch will only take 10 seconds or less) :-)
There is a really nice trick to be had when editing "sparse" worksheets like this: use the AutoFilter and fill formulas down.  The fill down will skip hidden rows meaning you don't have to touch all of your empty rows.  This means minimal intrusion to your current worksheet to update things.

Some steps:

Select columns A:K and apply the Auto Filter (Data-> Filter)
Filter column K (the year) to exclude Blanks.  You should now have 30 visible rows which are only the headers.
Edit cell K1 to be 2015 like you want.  Select the header and the cells below it using the mouse or CTRL+SHIFT+<DOWN ARROW>.  Fill the 2015 down the sheet using CTRL+D or Home->Fill->Down in the Ribbon.
Change the formula in cell F1 to
=UPPER(TEXT(DATEVALUE(H1&" "&J1&", "&K1), "dddd"))

Open in new window

This will calculate the day name using the date info given.
Fill that new formula down through column F again using CTRL+SHIFT+DOWN and then CTRL+D.
Remove the filter and, voila, you have edited only the headers.

If you don't want the formula in there anymore, you can do a copy/paste values on column F to remove it.

You can think quickly apply these steps to the other sheets, copying that formula in all of them.
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

contrainAuthor Commented:
Hi byronwall,

When I highlight columns A_K and do Data - Filter, in the dialog box from the drop down arrow in K the only options are Select All and Blanks, but they are linked so you can either select both of them or neither, but since there is no data underneath until the next heading, if you select neither, the OK button is grayed out, and if you select both, it remains the same, no getting rid of the blank rows under the headings.
You should have Select All, 2014, and Blanks.  Just to confirm, you have selected the entire columns and then done the filter?  That is, you are dragging across the column headers (A through K) to get the full selection?

This is the same as asking for "A:K" in the Name Box / range selector thing.
Glenn RayExcel VBA DeveloperCommented:

Here are all four workbooks updated with each month for 2015.  I just used a modified version of my earlier formula to create new workbooks/sheets based on the data I created for Q1.

=IF(K1<>"",UPPER(TEXT(DATEVALUE(MID(K1,FIND(",",K1)+2,99))+DATE(0,4,-1),"DDDD, MMMM D, YYYY")),"")
(The bolded section would be tweaked each sheet to get the correct starting date.  Once set, the formula was copied down and resulting values replaced over the originals).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
contrainAuthor Commented:
This solution gave me the answers I was looking for.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.