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...
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...
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...
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...
At the core, this formula subtracts start time from end time to get duration in hours. This is done to calculate both work time and break time.
MOD(C6-B6,1) // get work time
MOD(E6-D6,1) // get break time
Next,...
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...
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...
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...
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...
This formula converts a numeric value in inches to text representing the same measurement in inches and feet. To get the value for feet, the INT function is used like this:
=INT(B5/12)&"' "
Inside INT...
The first thing this formula does is check the date in column B against the start date:
=IF(B4>=start
If the date is not greater than the start date, the formula returns zero. If the date is greater than or equal...
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...
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...
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...
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...