Blog Archives

Tom’s Tutorials For Excel: Summing Historic Numbers by Date, Excluding Weekends or Weekdays

Tom’s Tutorials For Excel: Summing Historic Numbers by Date, Excluding Weekends or Weekdays
When you have a list of numbers for previous dates, such as with payroll or sales activity, here is an example of how you can sum the past 14 days for weekdays only and for weekends only.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Calculating Negative Time Differences in Hundredths of a Second

Tom’s Tutorials For Excel: Calculating Negative Time Differences in Hundredths of a Second
When you want to calculate differences in time when the measurements are in hundredths of a second, special consideration must be given for cases when the result is negative. The next pictures show examples of calculations when the Actual time is less than expected;

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Conditionally Formatting Early, Late, and Acceptable Times

Tom’s Tutorials For Excel: Conditionally Formatting Early, Late, and Acceptable Times
Here is how you can use Conditional Formatting to identify times that are more than, or less than, a margin of acceptability. In the picture, the cells holding airline flight arrival times that occur more than 60 seconds earlier than their scheduled arrival time are shaded yellow.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function.

Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function
The INDIRECT function can refer to a named range for quick data analysis, especially if you don’t need or want to use a pivot table. In this example, columns B:F hold several years of daily sales activity for a department store.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , ,

Tom’s Tutorials For Excel: Reverse Lookup For Times in Milliseconds

Tom’s Tutorials For Excel: Reverse Lookup For Times in Milliseconds
You can format, analyze, and lookup (in this example, reverse VLOOKUP with CHOOSE) times measured in milliseconds.
In the first picture, times for a swimming race are measured in minutes, seconds, and milliseconds. The times in range B4:B12,

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates

Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates

In yesterday’s tutorial, I showed how to place a date and time on separate lines in the same cell.

That example involved a formula with the TEXT function,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell

Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell

Here’s a tip to place the date and time in the same cell, in separate lines.

The first step is to enter the formula
=TEXT(NOW(),”MMMM D,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Adding and Subtracting Time in Hours Minutes and Seconds

Tom’s Tutorials For Excel: Adding and Subtracting Time in Hours Minutes and Seconds

Formula examples for hours, minutes, and seconds being added or subtracted from time.

Hours
Example to add 3 hours: =$B$1+TIME(3,0,0)
Example to subtract 3 hours: =$B$1-TIME(3,0,0)

Minutes
Example to add 16 minutes: =$B$1+TIME(0,16,0)
Example to subtract 16 minutes: =$B$1-TIME(0,16,0)

Seconds
Example to add 48 seconds: =$B$1+TIME(0,0,48)
Example to subtract 48 seconds: =$B$1-TIME(0,0,48)

Combination of Hours,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Validating an Entry as a Real Date

Tom’s Tutorials For Excel: Validating an Entry as a Real Date

One way to verify that a bona fide date is being entered into a cell is to use Data Validation.

In the pictured example, dates are being entered into a list in column E.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Converting an Elapsed Time to a Decimal Number

Tom’s Tutorials For Excel: Converting an Elapsed Time to a Decimal Number

Some employers pay their employees based on work time that is measured in decimals as portions of an hour. For example, if an employee works a 7-hour and 45-minute day, the employer pays that person for 7.75 hours of work time.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Finding the First and Last Days of the Week and Month

Tom’s Tutorials For Excel: Finding the First and Last Days of the Week and Month

Here are formulas to return various dates of first and last days of a given month.

First day’s date of that month: =DATE(YEAR(B1),MONTH(B1),1)

Last day’s date of that month: =DATE(YEAR(B1),MONTH(B1)+1,0)

First Monday date of that month:
=DATE(YEAR(B1),MONTH(B1),8)-WEEKDAY(DATE(YEAR(B1),MONTH(B1),6))

Last Friday date of that month:
=DATE(YEAR(B1),MONTH(B1)+1,1)-WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,1)-6)

First day of the month,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Calculating Dates for Last Day of Current, Previous, and Future Months

Tom’s Tutorials For Excel: Calculating Dates for Last Day of Current, Previous, and Future Months

As pictured below, here are formulas that return the date for the last day of…
• Current month: =DATE(YEAR(NOW()),MONTH(NOW())+1,0)
• Last month: =DATE(YEAR(NOW()),MONTH(NOW()), 0)
• Next month: =DATE(YEAR(NOW()),MONTH(NOW())+2,0)

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Conditionally Formatting a Specific Weekday Date

Tom’s Tutorials For Excel: Conditionally Formatting a Specific Weekday Date

In Excel, the 7 days of the calendar week can be identified by their index number using the WEEKDAY function, starting from 1 (Sunday) to 7 (Saturday). For example, you can use Conditional Formatting to highlight cells with dates that fall on a Friday when the WEEKDAY function returns the number 6.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Times by the Minute

Tom’s Tutorials For Excel: Rounding Times by the Minute

Here are 3 formulas that round a time to its nearest minute, or up to the next minute, or down to the previous minute. This example uses the ten best times of the 2012 Boston Marathon. You’ll notice the number 1440 in the first formula,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Elapsed Time by 5 Minutes

Tom’s Tutorials For Excel: Rounding Elapsed Time by 5 Minutes

Here are 3 formulas that round an elapsed time by the 5-minute mark, in terms of nearest 5-minute mark, up to the next 5-minute mark, and down to the last (lower) 5-minute mark.

In the picture,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Quarter-Hour

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Quarter-Hour

Here are 3 formulas that round an elapsed time by the quarter-hour, in terms of nearest quarter-hour, up to the next quarter-hour, and down to the last (lower) quarter-hour.

In the picture, notice the differences for each employee’s elapsed time in decimalized quarter-hour segments,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Half-Hour

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Half-Hour

Here are 3 formulas that round an elapsed time by the half-hour, in terms of nearest half-hour, up to the next half-hour, and down to the last (lower) half-hour.

In the picture, notice the differences for each employee’s elapsed time in decimalized half-hour segments,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Times of Day by 5 Minutes

Tom’s Tutorials For Excel: Rounding Times of Day by 5 Minutes

Here are three pictures to show how a time of day can be rounded…
• To its nearest 5-minute mark.
• Up, to its next 5-minute mark.
• Down, to its last 5-minute mark.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Times of Day by the Quarter Hour

Tom’s Tutorials For Excel: Rounding Times of Day by the Quarter Hour

Here are three pictures to show how a time of day can be rounded…
• To its nearest quarter-hour.
• Up, to its next quarter-hour.
• Down, to its last quarter-hour.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Times of Day by the Half Hour

Tom’s Tutorials For Excel: Rounding Times of Day by the Half Hour

Here are three pictures to show how a time of day can be rounded…
• To its nearest half-hour.
• Up, to its next half-hour.
• Down, to its last half-hour.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Hour

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Hour

Here are 3 formulas that round an elapsed time by the hour, in terms of nearest hour, up to the next hour, and down to the last (lower) hour.

In the picture, notice the differences for each employee’s elapsed time in decimalized hours,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding all Cells in a Summed Range

Tom’s Tutorials For Excel: Rounding all Cells in a Summed Range

When you want to sum or average a range of cells that need to each be rounded beforehand, here’s an efficient alternative to rounding each cell individally. In the pictured example, daily rainfall amounts with one decimal are to be summed,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Separating Date and Time From NOW.

Tom’s Tutorials For Excel: Separating Date and Time From NOW.

Here’s how you can store the date in one cell, and the time in another cell, of a source cell that contains both a date and time, such as if that source cell were holding the NOW function.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Calculating Times Between Time Zones

Tom’s Tutorials For Excel: Calculating Times Between Time Zones

With the TIME function you can add or subtract times between time zones.
Then you can adjust the formula cells for a recognizable time format.

The formula in cell D3 copied down to cell D9 is
=IF(C3=0,B3-TIME(C3,,),B3+TIME(ABS(C3),,))

TimeDifferences_BeforeFormat_AfterFormat

Select the range,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Calculating Elapsed Hours Between Start and End Times

Tom’s Tutorials For Excel: Calculating Elapsed Hours Between Start and End Times

Here are two formulas showing elapsed time in hours between a Start Time and an End Time.

In the first picture, the formula in cell E1 is =(B2-A2)*24.
You can format that cell as Number and set as many or few decimals as you like.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Verifying a Time as AM or PM

Tom’s Tutorials For Excel: Verifying a Time as AM or PM

Here’s how you can verify a cell’s time value as either morning (AM), or afternoon or evening (PM). This is useful if you encounter a cell whose formatting does not show the time itself, such as in the pictured example below.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Setting Alignments For Date Left and Time Right in a Single Cell

Tom’s Tutorials For Excel: Setting Alignments For Date Left and Time Right in a Single Cell

If you have the date and time in a single cell, such as when you enter the NOW function, you can custom format the cell as MMM D, YYYY * H:MM AM/PM to separate the date (flush left inside the cell),

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Using the YEARFRAC Function For Fractional Years Between Dates

Tom’s Tutorials For Excel: Using the YEARFRAC Function For Fractional Years Between Dates

The YEARFRAC function returns a decimalized number as a fraction of year(s) that represents the count of whole days betweeen the start date (“Hire Date” in the pictured example), and the end date (today’s date in cell F1).

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Formatting Dates as Past or Future

Tom’s Tutorials For Excel: Formatting Dates as Past or Future

Here’s how you can mark dates in the past and/or in the future, based on some time interval such as less than or more than 30 days from today’s date. For example, in the first picture,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Converting Time to Minutes and Seconds

Tom’s Tutorials For Excel: Converting Time to Minutes and Seconds

Here is how you can take an elapsed time originally represented in Hours, Minutes, and Seconds, and show it as just Minutes, or as Minutes and Seconds. For example, the picture shows the top 10 finishing times in column B for runners of the 2012 Boston Marathon.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Calculating the Date for Easter

Tom’s Tutorials For Excel: Calculating the Date for Easter

Easter (often referred to as Easter Sunday) is a Christian festival and holiday. Easter always falls on a Sunday, but its date varies depending on the calendar year and the phase of the moon.

Specifically,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Calculating a Month’s Fiscal Quarter Number

Tom’s Tutorials For Excel: Calculating a Month’s Fiscal Quarter Number

Most businesses account their annual operations on a January to December schedule. For those businesses, their quarterly calendar looks like this:
Quarter 1: January, February, March.
Quarter 2: April, May, June.
Quarter 3: July,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Formatting a Date With Text

Tom’s Tutorials For Excel: Formatting a Date With Text

Here’s how you can enter a date in a cell, and apply a custom date format to make the cell look as if it contains text. You can use that date in math functions elsewhere, as shown below.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: AutoFilling Dates For Weekdays Only

Tom’s Tutorials For Excel: AutoFilling Dates For Weekdays Only

Here’s how you can fill in a range of dates that only includes traditional workdays of Monday through Friday, while bypassing the weekend dates for Saturdays and Sundays.

Step 1
You start by entering your beginning date into a cell.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Entering a Static Date and Time

Tom’s Tutorials For Excel: Entering a Static Date and Time

As you probably know, you can enter =TODAY() and get the current date, or =NOW() and format the cell as a time to get the current time.

Those functions belong to a category of volatile functions,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: What time is it *Really*? Ask the US Navy With a Web Query

Tom’s Tutorials For Excel: What time is it *Really*? Ask the US Navy With a Web Query

What is today’s date, and what is the current time of day? Here, you create a Web query to import a display of the current day and time for several North American time zones.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing to Today’s Date With INDIRECT

Tom’s Tutorials For Excel: Summing to Today’s Date With INDIRECT

Here’s an example of using the INDIRECT and SUM functions to add numbers from the top of column B to the row where today’s date (or whatever date you specify) is found in column A.

The formula in cell E2 is
=SUM(INDIRECT(“B1:B”&MATCH(E1,A:A,0)))

If you want to bypass the helper cell for today’s date,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Calculating Negative Time

Tom’s Tutorials For Excel: Calculating Negative Time

Here’s an example for calculating the difference between times, and expressing a negative result when a larger time is subtracted from a smaller time.

In the picture, runners are listed for comparing their 1000 meter run times against the record time.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Sorting Birthdays, Excluding the Year

Tom’s Tutorials For Excel: Sorting Birthdays, Excluding the Year

Companies and organizations often keep a list of their members’ birth dates, for purposes of sending out birthday greetings or managing employees’ birthdays with a complimentary day off work.

As the above picture shows,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Filtering Dates

Tom’s Tutorials For Excel: Filtering Dates

When it comes to filtering dates, a little VBA goes a long way in dealing with the nemesis of seemingly countless different formats a date can be represented in Excel.

In the picture, an obnoxious myriad of dates is shown in the Date column to demonstrate how,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Populating a ComboBox For a Range of Years

Tom’s Tutorials For Excel: Populating a ComboBox For a Range of Years

This populates a UserForm ComboBox with years that start 10 years ago and end 10 years from now. The idea is to reasonably control the available year which a user can select. At each passing new year the code will adjust the relative list.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Stamping Your Worksheet Changes With Date and Time

Tom’s Tutorials For Excel: Stamping Your Worksheet Changes With Date and Time

Suppose your worksheet requires frequent entries in a column, and you want to automatically record the date and time of whenever a cell in that column changes.

In the picture,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Make Dates look Familiar When Combined With Words In A Formula

Tom’s Tutorials For Excel: Make Your Dates Readable When Combined With Text

When combining dates with words in a formula, dates don’t always look like dates in the result.

For example, the above picture shows a date in cell A1.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing Time

Tom’s Tutorials For Excel: Summing Time

In the pictures, the formula in cell B3 is =B2-B1 which returns the elapsed time in hours and minutes between the Start time and the End time. You want the visual value 6:45 (meaning six hours and forty-five minutes), but instead Excel gives you an elapsed time that looks like a time of day,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Workbook Creation Date

Tom’s Tutorials For Excel: Workbook Creation Date

If you want to know the creation date and time of the active workbook:

Sub MyCreationDate()
Dim myDate As Date
myDate = ActiveWorkbook.BuiltinDocumentProperties(“Creation date”).Value
MsgBox Format(myDate, “mmmm d, yyyy at h:mm:ss AM/PM”), , _
“I was born on…”
End Sub

If you want to know the creation date and time of a closed or other workbook:

Sub CreationDateWB()
Dim objWB As Object,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: The INT Function

Tom’s Tutorials For Excel: The INT Function

The INT function rounds a number down to the nearest integer.
Example, in cell A1 is 2.7. In cell B1, the formula =INT(A1) returns 2.

For negative numbers, INT returns the next number away from zero.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: The NETWORKDAYS Function

Tom’s Tutorials For Excel: The NETWORKDAYS Function

In many business situations, days must be counted as “business days” rather than as “calendar days” to represent the actual non-weekend, non-holiday days that have passed between two dates. For this purpose, the NETWORKDAYS function is perfect.

In the next picture,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: More Date Formulas

Tom’s Tutorials For Excel: More Date Formulas

Following up on yesterday’s theme, here are more common scenarios involving date calculations. Referring to the picture…

…The formula in cell B3 that is copied across to cell G3 for last day of month, first day of next month,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Date Formulas

Tom’s Tutorials For Excel: Date Formulas

Here are a few date formulas for common situations. Referring to the picture…

…The formula for text and date in cell C1 is
=”Today’s date is “&A2
The better formula in cell C2 is
=”Today’s date is “&TEXT(A2,”MMM DD,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: ListBox Days & ComboBox Months

Tom’s Tutorials For Excel: ListBox Days & ComboBox Months

In your userform’s module, this Initialize event code populates a ListBox with days of the week, and a ComboBox with months of the year, as seen by example in the picture.

Private Sub UserForm_Initialize()

‘Populate a ListBox with days of the week.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,