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
  • 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.
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.


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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
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 Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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.

821 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