Time and Date

How Many Years, Months and Days Has it Been?

January 12, 2011 Formulas

The DATEDIF Function comes in handy when you want to know how many years, months, and days there are between two dates. And even though it may be slightly flawed, it still beats the alternatives. The following spreadsheet works well for birthdays or anniversaries. The DATEDIF Function is being used to generate the Years, Months, [...]

Get the full story …

Calculate Hours Between Two Dates and Times in Excel

December 10, 2010 Formulas

Recently I was asked how to calculate the number of hours between two points in time on different days. Since this was in a reader comment, I gave a brief answer that requires a fuller account here. Dates and Times are all part of the master plan in Excel. Once you “get” the fundamentals, the [...]

Get the full story …

Calculate the Xth Weekday of Any Month in Excel

December 8, 2010 Advanced

I’ve a simple formula for calculating the Xth Weekday of ANY month. It takes four inputs: Year, Month, Week, and Day. And requires a couple of lookup tables for data validation, one with special formatting. The History It all started when my wife mentioned there were a couple of meetings she had to schedule at [...]

Get the full story …

Convert Pace to MPH and Back Again in Excel

November 24, 2010 Formulas

Excel is a great tool for figuring stuff out, like for conversions that aren’t easy to do in your head. Here I’m converting Pace to MPH and then reversing the process, converting MPH to Pace, to create a conversion chart. My Conversion Problem I track my Average Pace when out walking for exercise by using [...]

Get the full story …

Convert Decimal Hours to Time in Excel

October 7, 2010 Intermediate

Converting a decimal time value to minutes and hours is easily done in Excel. That is, if the time value is less than 24 hours. Here are three simple steps. Enter a decimal value into a cell Divide by 24 Change cell formatting to Time (h:mm) To change the cell format use keyboard shortcut Ctrl+1 [...]

Get the full story …

The WEEKDAY Function in Excel

September 2, 2010 Formulas

Excel 2010 has new return_type arguments for the WEEKDAY Function. How useful they are is debatable, but you never know when you might come across a particular need. The WEEKDAY Function in has two arguments: serial_number and return_type. The serial_number argument is simply a number that represents a date. The return_type argument is optional, and [...]

Get the full story …

Date and Time Calculation in Excel

August 31, 2010 Beginner

The unit of time in Excel is the Day. By using the NOW Function to show both date and time, you can see the underlying serial number by changing the cell format to General. This serial number is composed of two parts: the date part is the integer and the time part is the decimal. [...]

Get the full story …

Convert a Text String Date to Date Serial Number

August 10, 2010 Formulas

In previous posts I’ve been going through some formulas designed to extract and convert a text string Date, to a Date serial number. Extract a Date from Text in Excel The MID Function in Excel The RIGHT Function in Excel CONCATENATE Function or Ampersand Operator in Excel The TYPE Function in Excel The VALUE Function [...]

Get the full story …

The VALUE Function in Excel – Converting Text Dates

August 7, 2010 Formulas

The VALUE Function formula in cell G2 has converted the text string Date from cell E2, into a date serial number. =VALUE(E2) Until recently, I always used the VALUE Function to do this conversion. It’s something that’s well documented in the Function Arguments. Alternative Formulas to Convert Text Dates However, all that’s required to change [...]