Date & Time Formulas
| Add business days to a date | Date - Add or Subtract |
| Add business days to a date changing the days of the weekend | Date - Add or Subtract |
| Add business days to a date excluding public holidays | Date - Add or Subtract |
| Add days to a date including weekends | Date - Add or Subtract |
| Add days to a date not landing on a weekend | Date - Add or Subtract |
| Add hours to a time | Time - Add or Subtract |
| Add minutes to a time | Time - Add or Subtract |
| Add months to a date | Date - Add or Subtract |
| Add seconds to a time | Time - Add or Subtract |
| Add weeks to a date | Date - Add or Subtract |
| Add years to a date | Date - Add or Subtract |
| Convert a date string to a date value | Date - Convert |
| Convert decimal hours to time | Time - Convert |
| Convert decimal minutes to time | Time - Convert |
| Convert decimal seconds to time | Time - Convert |
| Convert time to hours | Time - Convert |
| Convert time to minutes | Time - Convert |
| Convert time to seconds | Time - Convert |
| Convert time to time zone | Time - Convert |
| Number of business days between 2 dates | Date - Number of Days |
| Number of business days in a particular month | Date - Number of Days |
| Number of business days in a particular year | Date - Number of Days |
| Number of business days left in the current month | Date - Number of Days |
| Number of business days left in the current year | Date - Number of Days |
| Number of business days that have passed in the current month | Date - Number of Days |
| Number of business days that have passed in the current year | Date - Number of Days |
| Number of days between 2 dates | Date - Number of Days |
| Number of days between 2 dates excluding weekends | Date - Number of Days |
| Number of days between 2 dates ignoring months and years | Date - Number of Days |
| Number of days between 2 dates ignoring years | Date - Number of Days |
| Number of days in a particular month | Date - Number of Days |
| Number of days in a particular year | Date - Number of Days |
| Number of days left in the current month | Date - Number of Days |
| Number of days left in the current year | Date - Number of Days |
| Number of days months years from a date | Date - Number of Days |
| Number of days that have passed in the current month | Date - Number of Days |
| Number of days that have passed in the current year | Date - Number of Days |
| Number of Mondays between 2 dates | Date - Number of Days |
| Number of months and days between 2 dates | Date - Number of Months |
| Number of months between 2 dates | Date - Number of Months |
| Number of months between 2 dates ignoring days and years | Date - Number of Months |
| Number of months weeks days between 2 dates | Date - Number of Months |
| Number of occurrences of a particular day of the week | Date - Number of |
| Number of weekend days between 2 dates | Date - Number of Days |
| Number of weekend days in a particular month | Date - Number of Days |
| Number of weekend days in a particular year | Date - Number of Days |
| Number of weeks and days between 2 dates | Date - Number of Weeks |
| Number of weeks between 2 dates | Date - Number of Weeks |
| Number of working hours between 2 dates | Time - Number of Hours |
| Number of working hours between 2 times | Time - Number of Hours |
| Number of years and days between 2 dates | Date - Number of Years |
| Number of years between 2 dates | Date - Number of Years |
| Number of years months days between 2 dates | Date - Number of Years |
| Number of years months days from a date | Date - Number of Years |
| Return a date from a date string | Date - Display |
| Return a date in a particular format | Date - Display |
| Return a date in US date format | Date - Display |
| Return a list of random dates in a particular range | Date - Display |
| Return a persons age in years | Date - Display |
| Return a persons age in years months days | Date - Display |
| Return random dates in a particular range | Date - Display |
| Return the 3rd Monday in a month | Date - Display |
| Return the business days | Date - Display |
| Return the current date | Date - Display |
| Return the current date and time | Date - Display |
| Return the current day | Date - Display |
| Return the current month | Date - Display |
| Return the current quarter | Date - Display |
| Return the current time | Time - Display |
| Return the current year | Date - Display |
| Return the date from a date and time | Date - Display |
| Return the date from a date string | Date - Display |
| Return the date of the Monday in the current week | Date - Display |
| Return the date of the Monday in the next week | Date - Display |
| Return the date of the Monday in the previous week | Date - Display |
| Return the day from a date | Date - Return |
| Return the day name | Date - Display |
| Return the first business day of the year | Date - First |
| Return the first day of the current month | Date - First |
| Return the first day of the current quarter | Date - First |
| Return the first day of the next month | Date - First |
| Return the first day of the previous month | Date - First |
| Return the first day of the previous quarter | Date - First |
| Return the first Monday of the current month | Date - First |
| Return the first Monday of the next month | Date - First |
| Return the first Monday of the previous month | Date - First |
| Return the first weekday of the month | Date - First |
| Return the first weekday of the year | Date - First |
| Return the last business day of the year | Date - Last |
| Return the last day of the current month | Date - Last |
| Return the last day of the current quarter | Date - Last |
| Return the last day of the next month | Date - Last |
| Return the last day of the previous month | Date - Last |
| Return the last day of the previous quarter | Date - Last |
| Return the last Monday of the current month | Date - Last |
| Return the last Monday of the next month | Date - Last |
| Return the last Monday of the previous month | Date - Last |
| Return the last weekday of the month | Date - Last |
| Return the last weekday of the year | Date - Last |
| Return the month from a date | Date - Return |
| Return the most recent Friday* | Date - Return |
| Return the most recent Monday* | Date - Return |
| Return the next business day | Date - Return |
| Return the next Friday | Date - Return |
| Return the next Monday | Date - Return |
| Return the next working day | Date - Return |
| Return the nth business day of the year | Date - Return |
| Return the nth day of a month | Date - Return |
| Return the nth day of a week | Date - Return |
| Return the nth day of a year | Date - Return |
| Return the number corresponding to a given month | Date - Display |
| Return the previous business day | Date - Return |
| Return the previous Friday | Date - Return |
| Return the previous Monday | Date - Return |
| Return the previous working day | Date - Return |
| Return the time elapsed between 2 times | Time - Display |
| Return the time from a date and time | Time - Display |
| Return the time rounded to the nearest 15 minutes | Time - Display |
| Return the week number of the current date | Date - Return |
| Return the working hours between 2 dates | Time - Display |
| Return the working hours between 2 times | Time - Display |
| Return the year from a date | Date - Return |
| Subtract business days from a date | Date - Add or Subtract |
| Subtract business days from a date changing the days of the weekend | Date - Add or Subtract |
| Subtract business days from a date excluding public holidays | Date - Add or Subtract |
| Subtract days from a date | Date - Add or Subtract |
| Subtract hours from a time | Date - Add or Subtract |
| Subtract minutes from a time | Date - Add or Subtract |
| Subtract months from a date | Date - Add or Subtract |
| Subtract seconds from a time | Date - Add or Subtract |
| Subtract weeks from a date | Date - Add or Subtract |
| Subtract years from a date | Date - Add or Subtract |
| Test if a cell is between 2 dates | Date - Test |
| Test if a date is in a particular range | Date - Test |
| Test if a month has 30 days | Date - Test |
| Test if a month has 31 days | Date - Test |
| Test if a year is a leap year | Date - Test |
| Add business days to a date Date - Add or Subtract |
| Add business days to a date changing the days of the weekend Date - Add or Subtract |
| Add business days to a date excluding public holidays Date - Add or Subtract |
| Add days to a date including weekends Date - Add or Subtract |
| Add days to a date not landing on a weekend Date - Add or Subtract |
| Add hours to a time Time - Add or Subtract |
| Add minutes to a time Time - Add or Subtract |
| Add months to a date Date - Add or Subtract |
| Add seconds to a time Time - Add or Subtract |
| Add weeks to a date Date - Add or Subtract |
| Add years to a date Date - Add or Subtract |
| Convert a date string to a date value Date - Convert |
| Convert decimal hours to time Time - Convert |
| Convert decimal minutes to time Time - Convert |
| Convert decimal seconds to time Time - Convert |
| Convert time to hours Time - Convert |
| Convert time to minutes Time - Convert |
| Convert time to seconds Time - Convert |
| Convert time to time zone Time - Convert |
| Number of business days between 2 dates Date - Number of Days |
| Number of business days in a particular month Date - Number of Days |
| Number of business days in a particular year Date - Number of Days |
| Number of business days left in the current month Date - Number of Days |
| Number of business days left in the current year Date - Number of Days |
| Number of business days that have passed in the current month Date - Number of Days |
| Number of business days that have passed in the current year Date - Number of Days |
| Number of days between 2 dates Date - Number of Days |
| Number of days between 2 dates excluding weekends Date - Number of Days |
| Number of days between 2 dates ignoring months and years Date - Number of Days |
| Number of days between 2 dates ignoring years Date - Number of Days |
| Number of days in a particular month Date - Number of Days |
| Number of days in a particular year Date - Number of Days |
| Number of days left in the current month Date - Number of Days |
| Number of days left in the current year Date - Number of Days |
| Number of days months years from a date Date - Number of Days |
| Number of days that have passed in the current month Date - Number of Days |
| Number of days that have passed in the current year Date - Number of Days |
| Number of Mondays between 2 dates Date - Number of Days |
| Number of months and days between 2 dates Date - Number of Months |
| Number of months between 2 dates Date - Number of Months |
| Number of months between 2 dates ignoring days and years Date - Number of Months |
| Number of months weeks days between 2 dates Date - Number of Months |
| Number of occurrences of a particular day of the week Date - Number of |
| Number of weekend days between 2 dates Date - Number of Days |
| Number of weekend days in a particular month Date - Number of Days |
| Number of weekend days in a particular year Date - Number of Days |
| Number of weeks and days between 2 dates Date - Number of Weeks |
| Number of weeks between 2 dates Date - Number of Weeks |
| Number of working hours between 2 dates Time - Number of Hours |
| Number of working hours between 2 times Time - Number of Hours |
| Number of years and days between 2 dates Date - Number of Years |
| Number of years between 2 dates Date - Number of Years |
| Number of years months days between 2 dates Date - Number of Years |
| Number of years months days from a date Date - Number of Years |
| Return a date from a date string Date - Display |
| Return a date in a particular format Date - Display |
| Return a date in US date format Date - Display |
| Return a list of random dates in a particular range Date - Display |
| Return a persons age in years Date - Display |
| Return a persons age in years months days Date - Display |
| Return random dates in a particular range Date - Display |
| Return the 3rd Monday in a month Date - Display |
| Return the business days Date - Display |
| Return the current date Date - Display |
| Return the current date and time Date - Display |
| Return the current day Date - Display |
| Return the current month Date - Display |
| Return the current quarter Date - Display |
| Return the current time Time - Display |
| Return the current year Date - Display |
| Return the date from a date and time Date - Display |
| Return the date from a date string Date - Display |
| Return the date of the Monday in the current week Date - Display |
| Return the date of the Monday in the next week Date - Display |
| Return the date of the Monday in the previous week Date - Display |
| Return the day from a date Date - Return |
| Return the day name Date - Display |
| Return the first business day of the year Date - First |
| Return the first day of the current month Date - First |
| Return the first day of the current quarter Date - First |
| Return the first day of the next month Date - First |
| Return the first day of the previous month Date - First |
| Return the first day of the previous quarter Date - First |
| Return the first Monday of the current month Date - First |
| Return the first Monday of the next month Date - First |
| Return the first Monday of the previous month Date - First |
| Return the first weekday of the month Date - First |
| Return the first weekday of the year Date - First |
| Return the last business day of the year Date - Last |
| Return the last day of the current month Date - Last |
| Return the last day of the current quarter Date - Last |
| Return the last day of the next month Date - Last |
| Return the last day of the previous month Date - Last |
| Return the last day of the previous quarter Date - Last |
| Return the last Monday of the current month Date - Last |
| Return the last Monday of the next month Date - Last |
| Return the last Monday of the previous month Date - Last |
| Return the last weekday of the month Date - Last |
| Return the last weekday of the year Date - Last |
| Return the month from a date Date - Return |
| Return the most recent Friday* Date - Return |
| Return the most recent Monday* Date - Return |
| Return the next business day Date - Return |
| Return the next Friday Date - Return |
| Return the next Monday Date - Return |
| Return the next working day Date - Return |
| Return the nth business day of the year Date - Return |
| Return the nth day of a month Date - Return |
| Return the nth day of a week Date - Return |
| Return the nth day of a year Date - Return |
| Return the number corresponding to a given month Date - Display |
| Return the previous business day Date - Return |
| Return the previous Friday Date - Return |
| Return the previous Monday Date - Return |
| Return the previous working day Date - Return |
| Return the time elapsed between 2 times Time - Display |
| Return the time from a date and time Time - Display |
| Return the time rounded to the nearest 15 minutes Time - Display |
| Return the week number of the current date Date - Return |
| Return the working hours between 2 dates Time - Display |
| Return the working hours between 2 times Time - Display |
| Return the year from a date Date - Return |
| Subtract business days from a date Date - Add or Subtract |
| Subtract business days from a date changing the days of the weekend Date - Add or Subtract |
| Subtract business days from a date excluding public holidays Date - Add or Subtract |
| Subtract days from a date Date - Add or Subtract |
| Subtract hours from a time Date - Add or Subtract |
| Subtract minutes from a time Date - Add or Subtract |
| Subtract months from a date Date - Add or Subtract |
| Subtract seconds from a time Date - Add or Subtract |
| Subtract weeks from a date Date - Add or Subtract |
| Subtract years from a date Date - Add or Subtract |
| Test if a cell is between 2 dates Date - Test |
| Test if a date is in a particular range Date - Test |
| Test if a month has 30 days Date - Test |
| Test if a month has 31 days Date - Test |
| Test if a year is a leap year Date - Test |
Add business days to a date |
| You can add business days to an existing date by using the WORKDAY.INTL function. |
|
| * This function will ignore Saturdays and Sundays by default. * 1 - Displays the current date. * 2 - Displays the date that is 7 business days after the date in cell "A1". * 3 - Displays the date that is 21 business days before the date in cell "A1". |
Add business days to a date changing the days of the weekend |
| You can add business days to an existing date by using the WORKDAY.INTL function. |
|
| * This function will default to excluding Saturdays and Sundays but this can be changed. * 1 - Displays the current date. * 2 - Displays 9 business days after the date in cell "A1" (excluding Saturday and Sunday with an explicit argument). * 3 - Displays 9 business days after the date in cell "A1" (excluding Friday and Saturday). * 4 - Displays 9 business days after the date in cell "A1" (excluding just Wednesday). |
Add business days to a date excluding public holidays |
| You can add a certain number of business days to an existing date by using the WORKDAY.INTL function. |
|
| * 1 - Displays the current date. * 2 - Displays the date of the first public holiday that we want to exclude. This is 2 business days after the date in cell "A1". * 3 - Displays the date of the second public holiday day that we want to exclude. This is 4 business days after the date in cell "A1". * 4 - Displays the date of the third public holiday day that we want to exclude. This is 6 business days after the date in cell "A1". * The WORKDAY.INTL function has an optional argument called "holidays" that allows you to exclude additional dates. * The public holiday dates that we want to exclude are in cells "A2:A4". * 5 - This formula displays the business date that is 5 business days after the date in cell "A1" and excludes three additional dates. * So the date displayed in cell "A5" is 8 business days after the date in cell "A1". |
Add days to a date including weekends |
| You can add a certain number of days to an existing date by adding that corresponding whole number. |
|
| * Dates are represented as date serial numbers. * DATEVALUE - The date serial number given a date in text format. * DAY - The day as an integer given a date serial number. * MONTH - The month as an integer given a date serial number. * NOW - The date serial number of the current system date and time. * T - The argument as a text string when the value is text. * TODAY - The date serial number representing today's date. * YEAR - The year as an integer given a date serial number. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays 1 day added to that date. * 4 - Displays 7 days added to that date. * All these cells have been formatted with the custom number format "dd dddd mmmm yyyy". |
Add days to a date not landing on a weekend |
| You can add a certain number of days to an existing date which will return the Monday when the date is either a Saturday or a Sunday. |
|
| * IF - The value based on whether a condition is True or False. * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * WEEKDAY - The day of the week for a given date. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays the number of days that you want to add. * 4 - Displays 6 days after the date in cell A1 * 5 - Displays this date formatted with "dddd" * 6 - Displays a number from 1 (Monday) to 7 (Sunday) representing the corresponding day of the week. * 7 - Displays the date which is a certain number of days after an existing date that ignores Saturdays and Sundays. * All these cells have been formatted with the custom number format "dddd mmmm yyyy". |
Add hours to a time |
| You can add or subtract a number of hours from a time using fractions. |
|
| * Dates are represented as sequential whole numbers so 1 hour is equivalent to the fraction (1/24). * Times are represented as fractions based on there being 24 hours in a day. * 1 Hour is 1/24 = 0.04166 * 1 Minute is 1/(24*60) = 1/1440 * 1 Second is 1/(24*60*60) = 1/86400 * NOW - The date serial number of the current system date and time. * TIME - The time as a decimal given an hour, minute, second. * TODAY - The date serial number representing today's date. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays the current time plus 6 hours. * 4 - Displays the current time plus 6 hours using the TIME function to return the correct decimal. * 5 - Displays the current time plus 8 hours and 15 minutes. * 6 - Displays the current time plus 8 hours and 15 minutes using the TIME function to return the correct decimal. * 7 - Displays the current time plus 13 hours, 25 minutes and 45 seconds. * 8 - Displays the current time plus 13 hours, 25 minutes and 45 seconds using the TIME function to return the correct decimal. * All these cells have been formatted with the custom number format "dddd, dd mmmm, yyyy hh:mm AM/PM". |
Add minutes to a time |
| You can add (or subtract) a number of minutes from a time using fractions. |
|
| * Dates are represented as sequential whole numbers so 1 hour is equivalent to the fraction (1/24). * Times are represented as fractions based on there being 24 hours in a day. * 1 Hour is 1/24 = 0.04166 * 1 Minute is 1/(24*60) = 1/1440 * 1 Second is 1/(24*60*60) = 1/86400 * NOW - The date serial number of the current system date and time. * TIME - The time as a decimal given an hour, minute, second. * TODAY - The date serial number representing today's date. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays the current time plus 25 minutes. * 4 - Displays the current time plus 25 minutes seconds using the TIME function to return the correct decimal. * 5 - Displays the current time plus 4 hours and 75 minutes. * 6 - Displays the current time plus 4 hours and 75 minutes using the TIME function to return the correct decimal. * 7 - Displays the current time plus 6 hours, 30 minutes and 80 seconds. * 8 - Displays the current time plus 6 hours, 30 minutes and 80 seconds using the TIME function to return the correct decimal. * All these cells have been formatted with the custom number format "dddd, dd mmmm, yyyy hh:mm AM/PM". |
Add months to a date |
| You can add a certain number of months to an existing date by adding the corresponding number of days in that month. |
|
| * Dates are represented as date serial numbers. * DATE - The date as a date serial number given a year, month, day. * DAY - The day as an integer given a date serial number. * EDATE - The date serial number that is a given number of months before or after a date. * MIN - The smallest numerical value. * MONTH - The month as an integer given a date serial number. * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * All these cells have been formatted with the custom number format "dd dddd mmmm yyyy". |
Add seconds to a time |
| You can add (or subtract) a number of seconds from a time using fractions. |
|
| * Dates are represented as sequential whole numbers so 1 hour is equivalent to the fraction (1/24). * Times are represented as fractions based on there being 24 hours in a day. * 1 Hour is 1/24 = 0.04166 * 1 Minute is 1/(24*60) = 1/1440 * 1 Second is 1/(24*60*60) = 1/86400 * NOW - The date serial number of the current system date and time. * TIME - The time as a decimal given an hour, minute, second. * TODAY - The date serial number representing today's date. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays the current time plus 200 seconds. * 4 - Displays the current time plus 200 seconds using the TIME function to return the correct decimal. * 5 - Displays the current time plus 40 minutes and 50 seconds. * 6 - Displays the current time plus 40 minutes and 50 seconds using the TIME function to return the correct decimal. * 7 - Displays the current time plus 3 hours, 15 minutes and 30 seconds. * 8 - Displays the current time plus 3 hours, 15 minutes and 30 seconds using the TIME function to return the correct decimal. * All these cells have been formatted with the custom number format "dddd, dd mmmm, yyyy hh:mm AM/PM". |
Add weeks to a date |
| You can add a certain number of weeks to an existing date by adding the corresponding number of days in a week. |
|
| * Dates are represented as date serial numbers. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. |
Add years to a date |
| You can add a certain number of years to an existing date by adding the corresponding number of days in that year. |
|
| * All these cells have been formatted with the custom number format "dd dddd mmmm yyyy". * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". |
Convert a date string to a date value |
| If you have any dates entered as a string (e.g. "01 Jul 2022") you can convert it to an actual date using the DATEVALUE function. |
|
| * This function returns the date serial number given a date in text format. * Exactly which formula you use will depend on your Regional Format. * If you regional format is set to English (United States) you need to use a different formula. * DATEVALUE - The date serial number given a date in text format. * LEFT - The first or left most characters in a text string. * MID - The characters from the middle of a text string. * NOW - The date serial number of the current system date and time. * RIGHT - The last or rightmost characters in a text string. * TEXT - The number as a formatted text string. * TODAY - The date serial number representing today's date. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Converts the date in cell "A1" to a text string with the format "yyyymmdd". * 4 - Converts the text string in cell "A3" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy". * 5 - Contains the text string "20200523". * 6 - Converts the text string in cell "A5" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy". * 7 - Contains the text string "20200208". * 8 - Converts the text string in cell "A7" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy". * 1 - Contains the text string "20200715". * 2 - Converts the text string in cell "A1" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy". * 3 - Contains the text string "20200523". * 4 - Converts the text string in cell "A3" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy". * 5 - Contains the text string "20200208". * 6 - Converts the text string in cell "A5" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy". |
Convert decimal hours to time |
| ?? |
|
| * ?? |
Convert decimal minutes to time |
| ?? |
|
| * ?? |
Convert decimal seconds to time |
| ?? |
|
| * ?? |
Convert time to hours |
| ?? |
|
| * multiply by 24 |
Convert time to minutes |
| ?? |
|
| * multiply by (24 x 60) |
Convert time to seconds |
| ?? |
|
| * multiply by (24 x 60 x 60) |
Number of business days between 2 dates |
| You can return the number of business days between two dates by using the NETWORKDAYS.INTL function. |
|
| * This function will include both the "start_date" and the "end_date" so you need to minus 1. * This function will ignore Saturdays and Sundays by default. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays the number of business days between that date and 1 day after. * 4 - Displays the number of business days between that date and 2 days after. * Cell "A10" displays the number of business days between 1 January 2020 and 20 January 2020. * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. * NETWORKDAYS.INTL - The number of working days between two dates excluding weekends and holidays. |
Number of business days in a particular month |
| You can return the number of business days in a particular month by using |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. |
Number of business days in a particular year |
| You can return the number of business days in a particular year by using |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. |
Number of business days left in the current month |
| You can return the number of business days left in the current month by combining 2 functions. |
|
| * The NETWORKDAYS.INTL function will ignore Saturdays and Sundays by default. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays the number of business days left in the month. * 4 - Displays the number of business days left in January after Monday 20th. * 5 - Displays the number of business days left in January after Monday 27th. * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. * EOMONTH - The date serial number of the last day of a month before or after a date. * NETWORKDAYS.INTL - The number of working days between two dates excluding weekends and holidays. |
Number of business days left in the current year |
| You can return the number of business days left in the current year by using |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. |
Number of business days that have passed in the current month |
| You can return the number of business days that have passed in the current month by using |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. |
Number of business days that have passed in the current year |
| You can return the number of business days that have passed in the current year by using |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. |
Number of days between 2 dates |
| You can return the number of days between 2 dates by subtracting them. |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. * DATEDIF - (Compatibility) The number of days, months or years between two dates. * DATEDIFFERENCE - Returns the number of days, months or years between two dates. |
Number of days between 2 dates excluding weekends |
| ?? |
|
| * You can use the NETWORKDAYS.INTL function if you want to return the total number of business days between two dates excluding weekends and public holidays * You can use the WORKDAY.INTL function if you want to return the serial number for a given number of working days before or after a date. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * NETWORKDAYS.INTL - The number of working days between two dates excluding weekends and holidays. * WORKDAY.INTL - The date serial number that is a given number of working days before or after a date. |
Number of days between 2 dates ignoring months and years |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATEDIFFERENCE - Returns the number of days, months or years between two dates. |
Number of days between 2 dates ignoring years |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DAYS - The number of days between two dates. * DATEDIFFERENCE - Returns the number of days, months or years between two dates. |
Number of days in a particular month |
| You must also provide the year |
|
| * There are four months that always contain 30 days and they are: April, June, September and November. * There is one month that always contains 28 days (except on a leap year) and that is February. * There is one month that always contains 29 days (only on a leap year) and that is February. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays the first day of February in the current year. * 4 - Displays the first day of April in the current year. * 5 - Displays the number of days in the current month of the current year using EOMONTH. * 6 - Displays the number of days in the current month of the current year using DATE, YEAR and MONTH. * 7 - Displays the number of days in the month of April of the current year using EOMONTH. * 8 - Displays the number of days in the month of April of the current year using DATE, YEAR and MONTH. * 9 - Displays the number of days in the month of February of the current year using EOMONTH. * 10 - Displays the number of days in the month of February in the leap year 2024. * DATE - The date as a date serial number given a year, month, day. * DAY - The day as an integer given a date serial number. * EOMONTH - The date serial number of the last day of a month before or after a date. * MONTH - The month as an integer given a date serial number. * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * YEAR - The year as an integer given a date serial number. * DAYSINAMONTH - Returns the number of days in a particular month and year. |
Number of days in a particular year |
| You can return the number of days in a year taking leap years into account. |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays 365 days before that date. * 4 - Displays (2*365) days before that date. * 5 - Displays (3*365) days before that date. * 6 - Displays the number of days in the current year. * 7 - Displays the number of days in the previous year. * 8 - Displays the number of days in the year of the date in cell "A4". * 9 - Displays the number of days in the year of the date in cell "A5". * 10 - Displays the number of days in the year 2010. * 11 - Displays the number of days in the year 2000. * DATE - The date as a date serial number given a year, month, day. * IF - The value based on whether a condition is True or False. * MOD - The remainder after division. * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * YEAR - The year as an integer given a date serial number. * DAYSINAYEAR - Returns the number of days in a particular year. |
Number of days left in the current month |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". |
Number of days left in the current year |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. * YEAR - The year as an integer given a date serial number. |
Number of days months years from a date |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". |
Number of days that have passed in the current month |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. |
Number of days that have passed in the current year |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. |
Number of Mondays between 2 dates |
| number of occurrences of a particular day of the week |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. |
Number of months and days between 2 dates |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. * DATEDIFFERENCE - Returns the number of days, months or years between two dates. |
Number of months between 2 dates |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. * DATEDIF - (Compatibility) The number of days, months or years between two dates. * DATEDIFFERENCE - Returns the number of days, months or years between two dates. |
Number of months between 2 dates ignoring days and years |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. * DATEDIFFERENCE - Returns the number of days, months or years between two dates. |
Number of months weeks days between 2 dates |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. * DATEDIFFERENCE - Returns the number of days, months or years between two dates. |
Number of occurrences of a particular day of the week |
| You can return the number of times a particular day of the week occurs between 2 dates (inclusive) |
|
| * The occurrences for represented as a numeric value, Monday = 1, Sunday = 7. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays the date formatted in long date format * 4 - Displays the number of days that you want to add * 5 - Displays the last date * 6 - Displays the date formatted in long date format * 7 - Displays the date * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. * SUMPRODUCT - The sum of the product of one or more arrays of values. * WEEKDAY - The day of the week for a given date. * ROW - The row number of a cell reference. * INDIRECT - The value of a given cell reference specified by a text string. |
Number of weekend days between 2 dates |
| Returns the number of Saturdays between the 2 dates |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. * SUMPRODUCT - The sum of the product of one or more arrays of values. * WEEKDAY - The day of the week for a given date. * ROW - The row number of a cell reference. * INDIRECT - The value of a given cell reference specified by a text string. |
Number of weekend days in a particular month |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. |
Number of weekend days in a particular year |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. |
Number of weeks and days between 2 dates |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * DATEDIF - (Compatibility) The number of days, months or years between two dates. * NOW - The date serial number of the current system date and time. * DATEDIFFERENCE - Returns the number of days, months or years between two dates. |
Number of weeks between 2 dates |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. * DATEDIFFERENCE - Returns the number of days, months or years between two dates. |
Number of working hours between 2 dates |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. |
Number of working hours between 2 times |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. |
Number of years and days between 2 dates |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. |
Number of years between 2 dates |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. |
Number of years months days between 2 dates |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". |
Number of years months days from a date |
| ?? |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". |
Return a date in a particular format |
| You can return a date in a particular format by using the TEXT function. |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays the current date in the format "dd/mm/yy". * 4 - Displays the current date in the format "mm/dd/yy". * 5 - Displays the date from cell "A2" in the format "dd mmmm, yyyy". * 6 - Displays the date from cell "A2" in the format "dd-mmm yyyy". * 7 - Displays the current date in the format "dd/mmm, yyyy". * 8 - Displays the date "01/07/2020" in the format "dddd mmm yy". * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * TEXT - The number as a formatted text string. |
Return a date in US date format |
| ?? |
|
| * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * TEXT - The number as a formatted text string. |
Return a persons age in years |
| Cell "A3" displays the date the person was born. |
|
| * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * TEXT - The number as a formatted text string. * YEARFRAC - The number of years as a decimal between two dates. * DATEDIF - (Compatibility) The number of days, months or years between two dates. * DATE - The date as a date serial number given a year, month, day. * INT - The number rounded down to the nearest integer. * AGE - Returns the age of a person given a birthday. |
Return random dates in a particular range |
| ?? |
|
| * INT - The number rounded down to the nearest integer. * RAND - The random number between zero and one (>=0 and <1). |
Return the current date |
| ?? |
|
| * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * (Ctrl + ;) - Enters the current date into the active cell and applies the format "dd/mm/yyyy" (Regional Settings). This is static and will not update. |
Return the current date and time |
| ?? |
|
| * NOW - The date serial number of the current system date and time. * (Ctrl + ;) - Enters the current date into the active cell and applies the format "dd/mm/yyyy" (Regional Settings). This is static and will not update. * (Ctrl + Shift + ;) - Enters the current time into the active cell. This is static and will not update. |
Return the current day |
| You can display the day of the week by using the TEXT function. |
|
| * If you do not want to display the full date you can apply the custom number format "dddd" to a cell that contains a date. * 1 - Display a date * 2 - Displays the date component of the date in cell A1 * 3 - Displays the date with the custom number format "dddd" applied * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * DAY - The day as an integer given a date serial number. * TEXT - The number as a formatted text string. * CHOOSE - The value in a row (or column) based on an index number. * WEEKDAY - The day of the week for a given date. |
Return the current month |
| ?? |
|
| * 1 - Display a date * 2 - Displays the date component of the date in cell A1 * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * MONTH - The month as an integer given a date serial number. * TEXT - The number as a formatted text string. |
Return the current quarter |
| You can return the current quarter with |
|
| * 1 - Displays a date. * All these cells have been formatted with the custom number format "dd dddd mmmm yyyy". * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. |
Return the current time |
| You can return the current time by using the NOW function. |
|
| * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * This function returns the serial number of the current system date and time. * (Ctrl + Shift + ;) - Enters the current time into the active cell * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. |
Return the current year |
| You can return the current year by using the YEAR function. |
|
| * This function returns the year as an integer given a date serial number. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * YEAR - The year as an integer given a date serial number. |
Return the date from a date and time |
| You can remove the time component from a date and time by using the TRUNC function. |
|
| * This function will return the number with any decimal places removed. * The INT function could also be used as it returns a number rounded down to the nearest integer. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays just the date from cell "A2". * 4 - Displays just the date from cell "A2". * INT - The number rounded down to the nearest integer. * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * TRUNC - The number rounded towards zero and truncated to a certain number of decimal places or digits. |
Return the date of the Monday in the current week |
| ?? |
|
| * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * WEEKDAY - The day of the week for a given date. |
Return the date of the Monday in the previous week |
| ?? |
|
| * ?? |
Return the date of the Monday in the next week |
| ?? |
|
| * ?? |
Return the day from a date |
| Display the current day |
|
| * ?? |
Return the day name |
| ?? |
|
| * ?? |
Return the first day of the current month |
| ?? |
|
| * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. * DATE - The date as a date serial number given a year, month, day. * YEAR - The year as an integer given a date serial number. * MONTH - The month as an integer given a date serial number. |
Return the first day of the previous month |
| ?? |
|
| * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. |
Return the first Monday of the current month |
| ?? |
|
| * Cell "A1" displays a date. * All these cells have been formatted with the custom number format "dd dddd mmmm yyyy". * TODAY - The date serial number representing today's date. * DATE - The date as a date serial number given a year, month, day. * DAY - The day as an integer given a date serial number. * MONTH - The month as an integer given a date serial number. * WEEKDAY - The day of the week for a given date. * YEAR - The year as an integer given a date serial number. |
Return the first weekday of the month |
| You can return the first weekday of the month |
|
| * 1 - Displays a date. * All these cells have been formatted with the custom number format "dd dddd mmmm yyyy". * CHOOSE - The value in a row (or column) based on an index number. * EOMONTH - The date serial number of the last day of a month before or after a date. * TODAY - The date serial number representing today's date. * WEEKDAY - The day of the week for a given date. |
Return the last day of the current month |
| You can return the last day in the month by using the EOMONTH function. |
|
| * 1 - Displays a date. * 2 - Displays the last day in this month. * 3 - Displays an alternative formula. * All these cells have been formatted with the custom number format "dd dddd mmmm yyyy". * DATE - The date as a date serial number given a year, month, day. * EOMONTH - The date serial number of the last day of a month before or after a date. * MONTH - The month as an integer given a date serial number. * TODAY - The date serial number representing today's date. |
Return the last day of the previous month |
| ?? |
|
| * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. * YEAR - The year as an integer given a date serial number. * TODAY - The date serial number representing today's date. * MONTH - The month as an integer given a date serial number. |
Return the last day of the previous quarter |
| ?? |
|
| * DATE - The date as a date serial number given a year, month, day. * NOW - The date serial number of the current system date and time. * YEAR - The year as an integer given a date serial number. * TODAY - The date serial number representing today's date. * MONTH - The month as an integer given a date serial number. |
Return the last weekday of the month |
| ?? |
|
| * You can return the last weekday of the month * Cell "A1" displays a date. * All these cells have been formatted with the custom number format "dd dddd mmmm yyyy". * EOMONTH - The date serial number of the last day of a month before or after a date. * MAX - The largest numerical value. * TODAY - The date serial number representing today's date. * WEEKDAY - The day of the week for a given date. |
Return the next Monday |
| You can return the next Monday using |
|
| * 1 - Displays a date * 2 - Displays the next Monday * 3 - Displays an alternative formula * All these cells have been formatted with the custom number format "dd dddd mmmm yyyy". * IF - The value based on whether a condition is True or False. * MOD - The remainder after division. * TODAY - The date serial number representing today's date. * WEEKDAY - The day of the week for a given date. |
Return the number corresponding to a given month |
| ?? |
|
| * NOW - The date serial number of the current system date and time. * TODAY - The date serial number representing today's date. * MONTH - The month as an integer given a date serial number. * CHOOSE - The value in a row (or column) based on an index number. * LEFT - The first or left most characters in a text string. * MATCH - (XMATCH) The position of a value in a list, table or cell range. |
Return the time elapsed between 2 times |
| ?? |
|
| * ?? |
Return the time from a date and time |
| You can remove the date component from a date and time by using the MOD function. |
|
| * This function will return the remainder after division. * The remainder after dividing a number by 1 will remove the integer part of the number. * There are some other formulas that could also be used. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays just the time from cell "A2". * 4 - Displays just the time from cell "A2". This formula uses subtraction to remove the date integer. * 5 - Displays just the time from cell "A2". This formula reconstructs a time using the individual components. * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. * MOD - The remainder after division. * TIME - The time as a decimal given an hour, minute, second. * INT - The number rounded down to the nearest integer. * HOUR - The hour as an integer given a date serial number. * MINUTE - The minute as an integer given a date serial number. * SECOND - The number of seconds as an integer given a date serial number. |
Return the week number of the current date |
| ?? |
|
| * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. * ISOWEEKNUM - The week number in the year for a given date (based on European convention). |
Subtract business days from a date |
| You can subtract business days from an existing date by using the WORKDAY.INTL function. |
|
| * This function will ignore Saturdays and Sundays by default. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays the date that is 7 business days before the date in cell "A1". * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. * WORKDAY.INTL - The date serial number that is a given number of working days before or after a date. |
Subtract business days from a date changing the days of the weekend |
| You can subtract business days from an existing date by using the WORKDAY.INTL function. |
|
| * This function will default to excluding Saturdays and Sundays. * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays 9 business days before the date in cell "A1" (excluding Saturday and Sunday with an explicit argument). * 4 - Displays 9 business days before the date in cell "A1" (excluding Friday and Saturday). * 5 - Displays 9 business days before the date in cell "A1" (excluding just Wednesday). * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. * WORKDAY.INTL - The date serial number that is a given number of working days before or after a date. |
Subtract business days from a date excluding public holidays |
| You can add a certain number of business days to an existing date by using the WORKDAY.INTL function. |
|
| * The public holidays that we want to exclude are in cells "A3:A5". * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays 2 business days before the date in cell "A1". * 4 - Displays 4 business days before the date in cell "A1". * 5 - Displays 6 business days before the date in cell "A1". * 6 - Displays the date that is 5 business days before the date in cell "A1" excluding the 3 public holidays. * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. * WORKDAY.INTL - The date serial number that is a given number of working days before or after a date. |
Subtract days from a date |
| You can subtract days from a date by |
|
| * Cell "A1" displays the current date. * Cell "A2" displays the current date and time. * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. |
Subtract hours from a time |
| You can subtract (or add) a number of hours from a time using fractions. |
|
| * Dates are represented as sequential whole numbers so 1 hour is equivalent to the fraction (1/24). * Times are represented as fractions based on there being 24 hours in a day. * 1 Hour is 1/24 = 0.04166 * 1 Minute is 1/(24*60) = 1/1440 * 1 Second is 1/(24*60*60) = 1/86400 * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays the current time minus 6 hours. * 4 - Displays the current time minus 6 hours using the TIME function to return the correct decimal. * 5 - Displays the current time minus 8 hours and 15 minutes. * 6 - Displays the current time minus 8 hours and 15 minutes using the TIME function to return the correct decimal. * 7 - Displays the current time minus 13 hours, 25 minutes and 45 seconds. * 8 - Displays the current time minus 13 hours, 25 minutes and 45 seconds using the TIME function to return the correct decimal. * All these cells have been formatted with the custom number format "dddd, dd mmmm, yyyy hh:mm AM/PM". * NOW - The date serial number of the current system date and time. * TIME - The time as a decimal given an hour, minute, second. * TODAY - The date serial number representing today's date. |
Subtract minutes from a time |
| You can subtract (or add) a number of minutes from a time using fractions. |
|
| * Dates are represented as sequential whole numbers so 1 hour is equivalent to the fraction (1/24). * Times are represented as fractions based on there being 24 hours in a day. * 1 Hour is 1/24 = 0.04166 * 1 Minute is 1/(24*60) = 1/1440 * 1 Second is 1/(24*60*60) = 1/86400 * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays the current time minus 25 minutes * 4 - Displays the current time minus 25 minutes seconds using the TIME function to return the correct decimal. * 5 - Displays the current time minus 4 hours and 75 minutes. * 6 - Displays the current time minus 4 hours and 75 minutes using the TIME function to return the correct decimal. * 7 - Displays the current time minus 6 hours, 30 minutes and 80 seconds. * 8 - Displays the current time minus 6 hours, 30 minutes and 80 seconds using the TIME function to return the correct decimal. * All these cells have been formatted with the custom number format "dddd, dd mmmm, yyyy hh:mm AM/PM". * NOW - The date serial number of the current system date and time. * TIME - The time as a decimal given an hour, minute, second. * TODAY - The date serial number representing today's date. |
Subtract months from a date |
| You can subtract months from a date by |
|
| * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. |
Subtract seconds from a time |
| You can subtract (or add) a number of seconds from a time using fractions. |
|
| * Dates are represented as sequential whole numbers so 1 hour is equivalent to the fraction (1/24). * Times are represented as fractions based on there being 24 hours in a day. * 1 Hour is 1/24 = 0.04166 * 1 Minute is 1/(24*60) = 1/1440 * 1 Second is 1/(24*60*60) = 1/86400 * 1 - Displays the current date. Custom format "dddd dd mmmm yyyy". * 2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss". * 3 - Displays the current time minus 200 seconds. * 4 - Displays the current time minus 200 seconds using the TIME function to return the correct decimal. * 5 - Displays the current time minus 40 minutes and 50 seconds. * 6 - Displays the current time minus 40 minutes and 50 seconds using the TIME function to return the correct decimal. * 7 - Displays the current time minus 3 hours, 15 minutes and 30 seconds. * 8 - Displays the current time minus 3 hours, 15 minutes and 30 seconds using the TIME function to return the correct decimal. * All these cells have been formatted with the custom number format "dddd, dd mmmm, yyyy hh:mm AM/PM". * NOW - The date serial number of the current system date and time. * TIME - The time as a decimal given an hour, minute, second. * TODAY - The date serial number representing today's date. |
Subtract years from a date |
| You can subtract years from a date by |
|
| * TODAY - The date serial number representing today's date. * NOW - The date serial number of the current system date and time. |
Test if a month has 30 days |
| ?? |
|
| * Test if a month has 31 days * Test if a year is a leap year |
Test if a month has 31 days |
| ?? |
|
| * Test if a month has 30 days * Test if a year is a leap year |
Test if a year is a leap year |
| ?? |
|
| * Test if a month has 31 days * Test if a month has 30 days |
© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited Top