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 additional holidays | Date - Add or Subtract |
| Add calendar days to a date | Date - Add or Subtract |
| Add calendar days to a date excluding additional dates | Date - Add or Subtract |
| Add calendar 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 additional holidays Date - Add or Subtract |
| Add calendar days to a date Date - Add or Subtract |
| Add calendar days to a date excluding additional dates Date - Add or Subtract |
| Add calendar 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 ignore Saturdays and Sundays by default, 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" (using the string representation). 4 - Displays 9 business days after the date in cell "A1" (excluding Friday and Saturday). 5 - Displays 9 business days after the date in cell "A1" (using the string representation). 6 - Displays 9 business days after the date in cell "A1" (excluding just Wednesday). 7 - Displays 9 business days after the date in cell "A1" (using the string representation). |
Add business days to a date excluding additional holidays |
| You can add 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 calendar days to a date |
| You can add calendar days to an existing date by adding that corresponding whole number. |
|
| This function will ignore Saturdays and Sundays by default. 1 - Displays the current date. 2 - Displays the date that is 5 calendar days after the date in cell "A1". 3 - Displays the date that is 5 calendar days before the date in cell "A1". 4 - This is an alternative formula. |
Add calendar days to a date excluding additional dates |
| You can add calendar days to an existing date by adding that corresponding whole number. |
|
| 1 - Displays the current date. 2 - Displays the date that is 20 calendar days after the date in cell "A1". 3 - Displays the date that is 20 calendar days after the date in cell "A1" and then adds another 3 days. 4 - Displays the date of the first additional date we want to exclude. 5 - Displays the date of the second additional date we want to exclude. 6 - Displays the date of the third additionl date we want to exclude. The three additional dates that we want to exclude are in cells "A4:A6". 7 - This formula displays the calendar date that is 20 days after the date in cell "A1" and excludes three additional dates. |
Add calendar days to a date not landing on a weekend |
| You can add calendar days to an existing date not landing on a weekend. |
|
| This formula will return the following Monday when the date falls on either a Saturday or a Sunday. 1 - Displays the current date. The WEEKDAY function can be used to return the day of the week for a given date. 2 - Displays a number indicating the day of the week. The numbers are: 1 = Mon, 2 = Tue, 3 = Wed, 4 = Thu, 5 = Fri, 6 = Sat, 7 = Sun. You can add calendar days to an existing date by adding that corresponding whole number. 3 - Displays the date that is 6 days after the date in cell "A1". We now need to check if this date falls on a Saturday or a Sunday. 4 - Tells us if this date falls on a Saturday or a Sunday. If it does then we need to shift the date to the Monday. When its a Saturday we need to add 2 days and when its a Sunday we need to add 1 day. We can calculate this by subtracting the weekday number from 8. When its a Saturday the weekday number is 6 and when its a Sunday the weekday number is 7. 5 - Displays the date which is a certain number of days after an existing date that ignores Saturdays and Sundays. 6 - This formula uses a LET function. |
Add hours to a time |
| You can add hours to a time using fractions and the TIME function. |
|
| Dates are represented as sequential whole numbers so 1 hour is equivalent to the fraction (1/24). One Hour is 1/24 = 0.04166 One Minute is 1/(24*60) = 1/1440 One Second is 1/(24*60*60) = 1/86400 Times are represented as fractions based on there being 24 hours in a day. 1 - Displays the current date and time. 2 - Displays the current time plus 6 hours. 3 - Displays the current time plus 6 hours using the TIME function to return the correct decimal. 4 - Displays the current time plus 8 hours and 15 minutes. 5 - Displays the current time plus 8 hours and 15 minutes using the TIME function to return the correct decimal. 6 - Displays the current time plus 13 hours, 25 minutes and 45 seconds. 7 - Displays the current time plus 13 hours, 25 minutes and 45 seconds using the TIME function to return the correct decimal. |
Add minutes to a time |
| You can add minutes to a time using fractions and the TIME function. |
|
| Dates are represented as sequential whole numbers so 1 hour is equivalent to the fraction (1/24). One Hour is 1/24 = 0.04166 One Minute is 1/(24*60) = 1/1440 One Second is 1/(24*60*60) = 1/86400 Times are represented as fractions based on there being 24 hours in a day. 1 - Displays the current date and time. 2 - Displays the current time plus 25 minutes. 3 - Displays the current time plus 25 minutes seconds using the TIME function to return the correct decimal. 4 - Displays the current time plus 4 hours and 75 minutes. 5 - Displays the current time plus 4 hours and 75 minutes using the TIME function to return the correct decimal. 6 - Displays the current time plus 6 hours, 30 minutes and 80 seconds. 7 - Displays the current time plus 6 hours, 30 minutes and 80 seconds using the TIME function to return the correct decimal. |
Add months to a date |
| You can add months to a date by using the EDATE function. |
|
| 1 - Displays the current date. 2 - Displays the date in cell "A1" plus 1 month. 3 - Displays the date in cell "A1" plus 3 months. The DATE function can be used to return a date serial number given a YEAR, MONTH, DAY. 4 - Displays the date in cell "A1" plus 20 months. |
Add seconds to a time |
| You can add seconds to a time using fractions and the TIME function. |
|
| Dates are represented as sequential whole numbers so 1 hour is equivalent to the fraction (1/24). One Hour is 1/24 = 0.04166 One Minute is 1/(24*60) = 1/1440 One Second is 1/(24*60*60) = 1/86400 Times are represented as fractions based on there being 24 hours in a day. 1 - Displays the current date and time. 2 - Displays the current time plus 200 seconds. 3 - Displays the current time plus 200 seconds using the TIME function to return the correct decimal. 4 - Displays the current time plus 40 minutes and 50 seconds. 5 - Displays the current time plus 40 minutes and 50 seconds using the TIME function to return the correct decimal. 6 - Displays the current time plus 3 hours, 15 minutes and 30 seconds. 7 - Displays the current time plus 3 hours, 15 minutes and 30 seconds using the TIME function to return the correct decimal. |
Add weeks to a date |
| You can add weeks to a date by adding the corresponding number of days. |
|
| 1 - Displays the current date. 2 - Displays the date that is 3 weeks after the date in cell "A1". 3 - Displays the date that is 20 weeks after the date in cell "A1". |
Add years to a date |
| You can add years to a date by using the EDATE function. |
|
| 1 - Displays the current date. 2 - Contains the date 29 February 2024 and 2024 was a leap year. 3 - This formula displays the date that is 4 years after the date in cell "A1". 4 - This formula displays the date that is 4 years after the date in cell "A2". 5 - This formula displays the date that is 1 year after 28 Feb 2023. 6 - This formula displays the date that is 1 year before 29 Feb 2024. |
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 |
| You can return the number corresponding to a given month using the MONTH function. |
|
| 1 - Displays the current date. 2 - Displays a date as a text string. 3 - Displays a month as a text string. 4 - Displays a abbreviated month as a text string. 5 - This formula displays the month number for the date in cell "A1". 6 - This formula displays the month number for the month in cell "A2". 7 - This formula displays the month number for the month in cell "A3". 8 - This formula displays the month number for the month in cell "A4". 9 - This is an alternative formula. |
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