Daily Dose of Excel

James shares his formula for determining which week of the month a particular date is in. He has a list of Sundays, each of which is the first day of the week. His formula:

ROUNDUP(((DAY(A1)-1)/7)+1,0)

James’ formula only works if A1 contains the first day of the week. If you want to calculate the week for any date, you’ll need a slightly longer formula. Here’s one that I created:

=ROUNDUP((DAY(A1)+WEEKDAY(DATE(YEAR(A1),MONTH(A1),0)))/7,0)

Both formulas assume that Sunday is the first day of the week.

Thanks for the formula James.

Bob has a workbook with several web queries in it. In each of the web queries, the first column is a “date”. He needs to find the minimum and maximum date for all web queries.

The first problem this presents is that the “dates” aren’t really dates; they’re text. That’s why I kept putting quotes around “date”. I’ll stop now. He could format the cells all he wants, but Excel won’t coerce them into dates. Even if he fixed the text to be real dates, when the web queries refresh, they’ll be right back to text. The dates look like this:

Dec 12, 2006, 5:00 pm

That seems like a great candidate for the DATEVALUE function, but sadly it doesn’t work. I needed to manipulate the text a little. I used this formula:

=DATEVALUE(LEFT(A4,FIND(”,”,A4,FIND(”,”,A4,1)+1)-1))+TIMEVALUE(MID(A4,FIND(”,”,A4,FIND(”,”,A4,1)+1)+2,255))

The DATEVALUE function converts the first part into a date, then adds it to the TIMEVALUE function, which converts the second part. The parts are divided by a comma. The second comma to be precise. The two FIND functions find the location of the second comma. The inner FIND sets the start for the outer FIND: One space to the right of the first comma found.

I put this formula in a column adjacent to the web query. I also checked the “Fill down formulas in columns adjacent to data” option in the Properties dialog box associated with the web query. This ensures that as the query grows, the formulas will fill down the right amount.

The second problem was using MIN and MAX across the sheets. The problem here was that some of the sheet names contain spaces. I knew that I needed some quotes around the sheet name, but I can never remember where. I ended up with this:

=MIN(’Abused News:Wipeout’!$F$4:$F$100)

The single quotes go around both sheet names (not individually) and before the bang. It took me a few iterations to figure that one out. If the queries ever get larger than 100 rows, I’m in trouble.

Over on Swimming Splits, Olly asks:

I am a rowing coach and am putting the data that my crew gives me from their training sessions into a spreadsheet in the mm:ss.00 format. However, if I make a mistake inputing the data e.g type 1:23.03 when it should be 1:23.30 something weird happens.

When I click in the cell, the value rounds up or down depending so 1:23.30 will round to 1:23.00. This will have a massive impact on the results that I am looking at. Is this a common occurrence in excel or is there something that I am doing wrong or should be pressing?

Good question. I’m not sure that I know this answer definitively, but I’ll tell you what I think based on what I see.

Dates and times are strange beasts in Excel. I should say that they are a strange beast because they are the same thing. As you probably already know, dates are stored as the number of days from a particular date, usually 31-Dec-1899. That means that 01-Jan-1900 is stored as 1 and 04-March-2007 is stored as 39,145. Similarly, times are stored as fractions of a day. While dates are integers, times of day are the fractions between the integers. Today may be 39,145, but today at 8:00AM is 39145.3333333. It’s only 8:00AM and already a third of the day is gone.

This method is great for adding, subtracting, and generally doing other math operations on dates. What it’s not good for is understandability by most humans. Excel attempts to bridge that gap by displaying dates and times as dates and times rather than these cryptic numbers. Note that when you enter 0:01.03 in a cell, the formula bar displays 12:00:01 AM. Usually the formula bar will tell you the truth regardless of how you’ve formatted the cells, but not in the case of dates and times. Like an over-protective mother, it shields you from the harsh realities. The noteworthy aspect of this example is that there are no hundredths in the formula bar. The hundredths are still stored, but they aren’t displayed in the formula bar.

If you were to edit the cell, say, by pressing F2 and Enter you lose the hundredths. Pressing Enter is the same as entering 12:00:01 AM into the cell, which contains no hundredths. To change the cell from 3 hundredths to 3 tenths, follow these steps: F2 to edit the cell, backspace three times to remove the AM and the preceding space, type .3, press Enter. I don’t know of any way to get the hundredths to display in the formula bar.

It seems rather easy for Microsoft to have done this differently. If a user enters fractions of seconds, display fractions of seconds. Otherwise display it as it is now. There doesn’t seem to be a trade-off here that I can see (other than coding time by developers).

Hi all

Together with Frank Rice (MS) I create two articles on MSDN.

Working with Excel Workbooks and Worksheets in E-Mail (update)
http://msdn2.microsoft.com/en-us/library/bb268022.aspx

Implementing Week-Numbering Systems and Date/Time Representations in Excel 2007
http://msdn2.microsoft.com/en-us/library/bb277364.aspx

For the week number article I used information from two pages on my website that I create together with Norman Harker.

Have a nice day

Ron de Bruin
http://www.rondebruin.nl/tips.htm

Here's what my (partially obfuscated) desktop looks like:

I put information I need on my desktop so I'm a Windows+M key combination away from what I need. The Excel file sits in my XLStart directory so the calendar gets updated every day.

VBA from Excel to Desktop

In the Workbook_Open event, I have

It only updates it once per day, although it doesn't really take that long to execute. In a standard module, I have:

The PastePicture call uses code from Stephen Bullen's PastePicture.zip file.

Almost nothing in the file is my own - all stolen. In addition to Stephen's PastePicture, the following code is borrowed:

The Calendar

The calendar for this month and next month is John Walkenbach's Array Calendar (see also). For 'next month' I merely added 1 to the month in the DATE function throughout that formula.

Week Numbers

The week numbers along the right come from Ron de Bruin's site. The formula in H27 is

=IF(LEN(B27)=0,"",INT((B27-DATE(YEAR(B27-WEEKDAY(B27-1)+4),1,3)+WEEKDAY(DATE(YEAR(B27-WEEKDAY(B27-1)+4),1,3))+5)/7))

It only shows the week number for weeks with a Monday (B27 in this case).

Highlighting Today and Holidays

The conditional formatting for the current month looks like this:

The first format inverts the colors for the current day. The second format checks the range HolVac to see if the date is a holiday - meaning a day I don't work. In that range, I have the holidays my company offers plus I enter any scheduled vacation days. I show the holiday formulas below. The second format is repeated for 'next month'.

Special Characters

Because I deal with German companies on a somewhat regular basis, I like to keep the ASCII codes for some German characters handy, and I do that across the bottom. To enter the special characters, hold down the Alt key and type the four digit number on the numeric keypad.

Holiday Formulas

The formulas for the holidays come from Chip Pearson's Holiday Page and Chip's Date and Time Page. In the following formulas, cell S1 contains the current year and S2 contains the next year.

New Year's Day: =IF(WEEKDAY(DATE(S1,1,1))=7,DATE(S1,1,1)-1,IF(WEEKDAY(DATE(S1,1,1))=1,DATE(S1,1,1)+1,DATE(S1,1,1)))

Next New Year's Day: =IF(WEEKDAY(DATE(S2,1,1))=7,DATE(S2,1,1)-1,IF(WEEKDAY(DATE(S2,1,1))=1,DATE(S2,1,1)+1,DATE(S2,1,1)))

Memorial Day: (array) =IF(SUM(IF(WEEKDAY(DATE($S$1,5,1)-1+ROW(INDIRECT("1:"&TRUNC(DATE($S$1,5,31)-DATE($S$1,5,1))+1)))=2,1,0))=5,
    DATE(YEAR(NOW()),5,1+((5-(2>=WEEKDAY(DATE(YEAR(NOW()),5,1))))*7)+(2-WEEKDAY(DATE(YEAR(NOW()),5,1)))),
    DATE(YEAR(NOW()),5,1+((4-(2>=WEEKDAY(DATE(YEAR(NOW()),5,1))))*7)+(2-WEEKDAY(DATE(YEAR(NOW()),5,1)))))

US Indpendence Day: =IF(WEEKDAY(DATE(S1,7,4))=7,DATE(S1,7,4)-1,IF(WEEKDAY(DATE(S1,7,4))=1,DATE(S1,7,4)+1,DATE(S1,7,4)))

Labor Day: =DATE(S1,9,1+((1-(2>=WEEKDAY(DATE(S1,9,1))))*7)+(2-WEEKDAY(DATE(S1,9,1))))

Thanksgiving: =DATE(S1,11,1+((4-(5>=WEEKDAY(DATE(S1,11,1))))*7)+(5-WEEKDAY(DATE(S1,11,1))))

Xmas Eve: =IF(WEEKDAY(DATE(S1,12,24))=7,DATE(S1,12,24)-1,IF(WEEKDAY(DATE(S1,12,24))=1,DATE(S1,12,24)-2,DATE(S1,12,24)))

Xmas: =IF(WEEKDAY(DATE(S1,12,25))=7,DATE(S1,12,25)-1,IF(WEEKDAY(DATE(S1,12,25))=1,DATE(S1,12,25)+1,DATE(S1,12,25)))

Update: Download DesktopPic.zip

I was talking with a workmate of mine and he mentioned that he is a swimming coach.
He told me that it's difficult to record swimming times in Excel.
His approach was to record Minutes, Seconds and Splits (Split Seconds) as separate columns. Now, I can only imagine what sort of frustrating (if not impressive!) formulas this led to.

I explained to him that Excel can store Split Seconds right out of the box.
Just format the cell as mm:ss.00

Great! That'll work.

Being the thinker, he suddenly realised typing in the times would be even more difficult than before. It's that fiddly colon key :

"Is there a way to type the times with a decimal point as a separator?" he asked.

Fair enough question. His times on paper are written as dot separated.

Fun! Let's write a formula!

I figured out there were 3 formats of time:
23 which means 23 seconds
23.45 which means 23.45 seconds
1.23.45 which means 1 minute and 23.45 seconds
The hour portion of the time is never reached.

So for a time typed into A1, the following formula turns it into an Excel time.
=IF(LEN(A1) - LEN(SUBSTITUTE(A1, ".", "")) = 2, TIMEVALUE("00:" & SUBSTITUTE(A1, ".", ":", 1)), TIMEVALUE("00:00:" & A1))

Again, the cell has a custom format of mm:ss.00