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

Posted on 2014-10-29
Last Modified: 2014-11-04
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.

Question by:contrain
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 27

Expert Comment

by:Glenn Ray
ID: 40411171
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.

LVL 15

Expert Comment

by:David L. Hansen
ID: 40411185
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) :-)

Expert Comment

ID: 40412135
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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 40413705
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.

Expert Comment

ID: 40413726
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.
LVL 27

Accepted Solution

Glenn Ray earned 500 total points
ID: 40413902

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).


Author Closing Comment

ID: 40422800
This solution gave me the answers I was looking for.

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question