Times in Excel are fractional values of the number 1. So, 12 PM is 12/24 = .5, 6:00 AM is 6/24 = .25, and so on. So, to convert a time by a given number, you need to divide the number of hours by 24 to get required...
This formula uses the MOD and COLUMN functions to filter out values not in nth columns, then runs MAX on the result.
The key is this snippet:
MOD(COLUMN(B5:K5)-COLUMN(B5)+1,L5)=0
Here, the formula uses the COLUMN...
Times in Excel are factional values of 24 hours. One hour of time is 1/24, and 1 minute of time is 1/(24*60) = 1/1440.
As a result, if you have a decimal value for 6 hours, and a time in A1, you can add 6 hours of...
Conditional formatting is evaluated for each cell in the range, relative to the upper left cell in the selection. In this case, the formula uses the MOD function to check the remainder of dividing the value in each...
Excel handles dates and times using a system in which dates are serial numbers and times are fractional values. For example, June 1, 2000 12:00 PM is represented in Excel as the number 36678.5, where 36678 is the date...
At the core, uses SUMPRODUCT to sum values in a row that have been "filtered" using logic based on MOD. The key is this:
MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0
This snippet of the formula uses the COLUMN function to...
When you use a formula to apply conditional formatting, the formula is evaluated for every cell in the selection. In this case, there are no addresses in the formula, so, for every cell in the data, the ROW and ISEVEN...
Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns TRUE, validation passes and the input is accepted. When the formula returns FALSE, validation fails and the...
The SUMPRODUCT function works directly with arrays.
One thing you can do quite easily with SUMPRODUCT is perform a test on an array using one or more criteria, then count the results.
In this case, we are running a...
The MOD function performs the modulo operation. It takes a number and a divisor and returns the remainder after division.
In this formula, we subtract one MOD result from another.
For the first MOD, we use the number...
Formulas that use the concept of "day of week" are based on the idea that each day of of the week has a specific number. By default, Excel assigns 1 to Sunday and 7 to Saturday.
In the example formula, B6 is the date...
The core of this formula is the MOD function. MOD takes a number and divisor, and returns the remainder after division, which makes it useful for formulas that need to do something every nth time.
In this case, the...
The MOD function returns the remainder after division. With a divisor of 1, MOD will return zero for any whole number.
We use this fact to construct a simple formula that tests the result of MOD. When the result is...
Times in Excel are factional values of 24 hours. One hour of time is 1/24, and 1 minute of time is 1/(24*60) = 1/1440.
As a result, if you have a decimal value for 15 minutes, and a time in A1, you can add 15 minutes...
Excel dates are serial numbers, and a day is equivalent to the number 1. This means 1 hour = 1/24 = 0.041666667. In other words, Excel times are just fractional numbers:
Time
Fraction
Hours...