MOD function in Excel

Excel Date and Time Functions return a dynamic date or time in a cell.

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… read more »

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… read more »

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… read more »

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… read more »

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… read more »

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… read more »

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… read more »

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… read more »

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:… read more »

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… read more »

Sidebar