Daily posts of Excel tips…and other stuff
Archive for the ‘Date Functions’ Category.
Ron de Bruin has an ISO Date page for computing ISO Week numbers, start dates, and end dates. I needed to compute the ISO year, which should just be the YEAR() function wrapped around the end date formula. But I was getting strange results for the first three days of January this year, [...]
I’m working on some VBA to take some of the drudgery out of payroll.
Public Function ThisFriday() As Date
ThisFriday = Date + 8 - Weekday(Date, vbFriday)
End Function
Public Function LastFriday() As Date
LastFriday = Date + 1 - Weekday(Date, vbFriday)
End Function
I thought I needed to know [...]
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 [...]
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 [...]
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 [...]
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
Private Sub Workbook_Open()
[...]
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 [...]
