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.
…
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;
…
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.
…
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.
…
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,
…
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,
…
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,
…
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,
…
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.
…
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.
…
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,
…
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)
…
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.
…
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,
…
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,
…
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,
…
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,
…
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.
…
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.
…
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.
…
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,
…
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,
…
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.
…
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),,))
Select the range,
…
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.
…
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.
…
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),
…
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).
…
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,
…
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.
…
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,
…
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,
…
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.
…
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.
…
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,
…
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.
…
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,
…
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.
…
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,
…
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,
…
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.
…
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,
…
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.
…
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,
…
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,…
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.
…
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,
…
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,
…
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,
…
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.
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
