Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Tag: MOD function

MOD function: Description, Usage, Syntax, Examples and Explanation

What is MOD function in Excel? MOD function is one of the Math and Trig functions in Microsoft Excel that returns the remainder after number is divided by divisor. The result has the same sign as divisor. Syntax of MOD function MOD(number, divisor) The MOD function syntax has the following arguments: Number: The number for which you want to find the…

Convert inches to feet and inches in Excel

To convert a measurement in inches to inches and feet (i.e. 53 to 4′ 5″) you can use a formula based on the INT and MOD functions. See illustration below: Formula =INT(A1/12)&”‘ “&MOD(A1)&”””” Explanation In the example shown, the formula in D5 is: =INT(B5/12)&”‘ “&MOD(B5,12)&”””” How this formula works This formula converts a numeric value in inches to text representing…

How to calculate most recent day of week in Excel

If you want to find the most recent Wednesday, or Saturday, or Monday, given a specific start date, you can use a formula that uses the MOD function. Formula =date-MOD(date-dow,7) Note: If the the date has the same day of week, date will be returned. Explanation   Formulas that use the concept of “day of week” are based on the…

Get work hours between dates and times in Excel

To calculate total work hours between two dates and times, you can use a formula based on the NETWORKDAYS function. Formula =(NETWORKDAYS(start,end)-1)*(upper-lower) +IF(NETWORKDAYS(end,end),MEDIAN (MOD(end,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(start,start) *MOD(start,1),upper,lower) Explanation In the example shown, E5 contains this formula: =(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN (MOD(C5,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(B5,B5) *MOD(B5,1),upper,lower) where “lower” is the named range H5 and “upper” is the named range H6. How this formula works This formula calculates total…

Calculate time difference in hours as decimal value in Excel

To get the duration between two times in as decimal hour (i.e. 3 hrs, 4.5 hrs, 8 hrs, etc.) you can use a formula based on the MOD function. Formula =MOD(end-start,1)*24 Explanation In the example shown, the formula in D5 is: =MOD(B2-A2,1)*24 Excel hours In Excel, one day is the number 1, so 1 hour = 1/24 = 0.041666667. In…

Convert time to time zone in Excel

This tutorial show how to Convert time to time zone in Excel using the example below. To convert a time from one time zone to another, you can use a formula that converts hours entered as whole numbers to the decimal values that Excel recognizes as times.  Formula =MOD(time+(hours/24),1) Explanation of how this formula works In the example shown, the formula…

Extract time from a date and time in Excel

This tutorial show how to Extract time from a date and time in Excel using the example below. If you have dates with time values and you want to extract only the time portion (the fractional part), you can use a formula that uses the MOD function. Note: Excel handles dates and time using a scheme in which dates are serial numbers and…

Basic timesheet formula with breaks in Excel

This tutorials shows Basic timesheet formula with breaks in Excel. To calculate work hours, taking into account break time that needs to be subtracted, you can use a formula based on the MOD function. MOD is used to handle start and end times that cross midnight. Formula =MOD(workstart-workend,1)-MOD(breakstart-breakend,1) Explanation In the example shown, the formula in F6 is: =MOD(C6-B6,1)-MOD(E6-D6,1) How this…

Calculate number of hours between two times in Excel

This tutorials shows how to calculate number of hours between two times in Excel. To calculate the number of hours between two times, you can use a formula that simply subtracts the start time from the end time. This is useful to calculate working time, calculate elapsed time, etc. However, when times cross a day boundary (midnight), things can get tricky.…

Add decimal hours to time in Excel

It you need to add decimal hours to time in Excel then this tutorials is for you. See example below. To add a given number of hours to a time, you can add hours divided by 24, or use the TIME function. Formula =time+(hours/24) Explanation In the example shown, the formula in D5 is: =B5+(C5/24) Note: make sure results are formatted as…

Add decimal minutes to time in Excel

It you need to add decimal minutes to time in Excel then this tutorials is for you. See example below: To add a given number of minutes to a time, you can add minutes divided by 1440, or use the TIME function. Formula =time+(minutes/1440) Explanation In the example shown, the formula in D5 is: =B5+(C5/1440) Note: make sure results are formatted…

Excel Get number at place value Example

This tutorials shows how to  get the number at a specific place value in Excel. To get the number at a specific place value you can use a formula based on the MOD function. By place value, we mean hundred thousands, ten thousands, thousands, hundreds, tens, ones, etc. Formula =MOD(number,place*10) – MOD(number,place) Explanation In the example shown, the formula in…

Excel Data validation number multiple 100

Accept only numbers that are multiple of 100 To allow only numbers in multiples of 100, you can use data validation with a custom formula based on the MOD function. Formula =MOD(A1,100)=0 Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case C5. Explanation…

Highlight every other row in Excel

This tutorial shows how to Highlight every other row in Excel using the example below; Formula =ISEVEN(ROW()) Explanation If you want to highlight every other row in a table (sometimes called zebra striping) with conditional formatting you can do so with a simple formula that uses either the ISEVEN or ISODD function. For example, if you want to highlight every…

Highlight multiples of specific value in Excel

This tutorial shows how to Highlight multiples of specific value in Excel using the example below; Formula =MOD(A1,value)=0 Explanation To highlight numbers that are multiples of a another number with conditional formatting, you can use a simple formula based on the MOD function. In the example shown, the formula used to highlight multiples of 9 is: =MOD(B4,$E$2)=0 Note: formula is entered…

Max of every nth column in Excel

This tutorial shows how to calculate Max of every nth column in Excel using the example below; Formula {=MAX(IF(MOD(COLUMN(range)-COLUMN(range.first)+1,n)=0,range))} Explanation To get the max of every nth column, you can use an array formula based on the MAX, MOD, and COLUMN functions. In the example shown, the formula in M5 is: =MAX(IF(MOD(COLUMN(B5:K5)-COLUMN(B5)+1,L5)=0,B5:K5)) Note: this is an array formula and must be entered with control…

Count cells that contain odd numbers in Excel

This tutorial shows how to Count cells that contain odd numbers in Excel using the example below; Formula =SUMPRODUCT(–(MOD(range,2)=1)) Explanation To count cells that contain only odd numbers, you can use a formula based on the SUMPRODUCT function together with the MOD function. In the example, the formula in cell E6 is: =SUMPRODUCT(–(MOD(rang,2)=1)) This formula returns 4 since there are…

Sum every nth column in Excel

This tutorial shows how to Sum every nth column in Excel using the example below; Formula =SUMPRODUCT(–(MOD(COLUMN(range)-COLUMN(range.first)+1,n)=0),range) Explanation To sum every nth column, you can use a formula based on the SUMPRODUCT, MOD, and COLUMN functions. In the example shown, the formula in L5 is: =SUMPRODUCT(–(MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0),B5:J5) How this formula works At the core, uses SUMPRODUCT to sum values in a…

Even and Odd function in Excel

This example illustrates the EVEN and the ODD function in Excel. It also shows you how to determine if a number is even or odd. Even 1. Rounds a positive number up to the nearest even integer. 2. Rounds a negative number down to the nearest even integer. Odd 1. Rounds a positive number up to the nearest odd integer. 2. Rounds a negative number…