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

Posted on 2014-10-29
Medium Priority
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.
Independent Software Vendors: 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 2000 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

741 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