Solved

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

Posted on 2014-10-29
7
142 Views
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.

Thanks,
Conference-Rooms-2015-Qtr-1.xlsx
0
Comment
Question by:contrain
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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.

Regards,
-Glenn
EE-Conference-Rooms-2015-Qtr-1.xlsx
0
 
LVL 15

Expert Comment

by:David L. Hansen
Comment Utility
I just used this:
=TEXT(L2,"DDDD")

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) :-)
0
 
LVL 3

Expert Comment

by:byronwall
Comment Utility
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:contrain
Comment Utility
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.
0
 
LVL 3

Expert Comment

by:byronwall
Comment Utility
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.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
Comment Utility
contrain,

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

Regards,
-Glenn
EE-Conference-Rooms-2015-Qtr-1.xlsx
EE-Conference-Rooms-2015-Qtr-2.xlsx
EE-Conference-Rooms-2015-Qtr-3.xlsx
EE-Conference-Rooms-2015-Qtr-4.xlsx
0
 

Author Closing Comment

by:contrain
Comment Utility
This solution gave me the answers I was looking for.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now