ultimate guide of Excel date functions 140 examples

 Below are 140 examples of different date functions, including data, formulas, and nested functions. These examples cover a wide range of use cases for date manipulation in Excel, Google Sheets, or similar spreadsheet tools.

---

1. Basic Date Functions

1. TODAY(): Returns the current date.

   • Formula: =TODAY()

   • Output: 10/25/2023 (if today's date is October 25, 2023)

2. NOW(): Returns the current date and time.

   • Formula: =NOW()

   • Output: 10/25/2023 14:30

3. DATE(year, month, day): Creates a date from year, month, and day.

   • Formula: =DATE(2023, 10, 25)

   • Output: 10/25/2023

4. DAY(date): Extracts the day from a date.

   • Formula: =DAY("10/25/2023")

   • Output: 25

5. MONTH(date): Extracts the month from a date.

   • Formula: =MONTH("10/25/2023")

   • Output: 10

6. YEAR(date): Extracts the year from a date.

   • Formula: =YEAR("10/25/2023")

   • Output: 2023

7. WEEKDAY(date): Returns the day of the week (1 = Sunday, 7 = Saturday).

   • Formula: =WEEKDAY("10/25/2023")

   • Output: 4 (Wednesday)

8. WEEKNUM(date): Returns the week number of the year.

   • Formula: =WEEKNUM("10/25/2023")

   • Output: 43

9. EDATE(start_date, months): Adds months to a date.

   • Formula: =EDATE("10/25/2023", 2)

   • Output: 12/25/2023

10. EOMONTH(start_date, months): Returns the last day of the month after adding months.

    • Formula: =EOMONTH("10/25/2023", 0)

    • Output: 10/31/2023

---

2. Date Arithmetic

11. Add days to a date:

    • Formula: ="10/25/2023" + 7

    • Output: 11/01/2023

12. Subtract days from a date:

    • Formula: ="10/25/2023" - 10

    • Output: 10/15/2023

13. Difference between two dates in days:

    • Formula: ="10/25/2023" - "10/10/2023"

    • Output: 15

14. Difference between two dates in months:

    • Formula: =DATEDIF("10/10/2023", "10/25/2023", "m")

    • Output: 0

15. Difference between two dates in years:

    • Formula: =DATEDIF("10/10/2020", "10/25/2023", "y")

    • Output: 3

16. Add months to a date:

    • Formula: =EDATE("10/25/2023", 5)

    • Output: 03/25/2024

17. Subtract months from a date:

    • Formula: =EDATE("10/25/2023", -3)

    • Output: 07/25/2023

18. Add years to a date:

    • Formula: =DATE(YEAR("10/25/2023") + 2, MONTH("10/25/2023"), DAY("10/25/2023"))

    • Output: 10/25/2025

19. Subtract years from a date:

    • Formula: =DATE(YEAR("10/25/2023") - 1, MONTH("10/25/2023"), DAY("10/25/2023"))

    • Output: 10/25/2022

20. Calculate age from birthdate:

    • Formula: =DATEDIF("05/15/1990", TODAY(), "y")

    • Output: 33 (if today is October 25, 2023)

---

3. Nested Date Functions

21. Extract the day of the week name:

    • Formula: =TEXT(WEEKDAY("10/25/2023"), "dddd")

    • Output: Wednesday

22. Get the first day of the month:

    • Formula: =DATE(YEAR("10/25/2023"), MONTH("10/25/2023"), 1)

    • Output: 10/01/2023

23. Get the last day of the month:

    • Formula: =EOMONTH("10/25/2023", 0)

    • Output: 10/31/2023

24. Check if a date is a weekend:

    • Formula: =IF(OR(WEEKDAY("10/25/2023")=1, WEEKDAY("10/25/2023")=7), "Weekend", "Weekday")

    • Output: Weekday

25. Calculate the next Monday from a date:

    • Formula: ="10/25/2023" + (8 - WEEKDAY("10/25/2023"))

    • Output: 10/30/2023

26. Calculate the previous Friday from a date:

    • Formula: ="10/25/2023" - (WEEKDAY("10/25/2023") + 1)

    • Output: 10/20/2023

27. Check if a year is a leap year:

    • Formula: =IF(MONTH(DATE(YEAR("10/25/2023"), 2, 29))=2, "Leap Year", "Not Leap Year")

    • Output: Not Leap Year

28. Get the quarter of the year for a date:

    • Formula: =ROUNDUP(MONTH("10/25/2023")/3, 0)

    • Output: 4

29. Calculate the number of workdays between two dates:

    • Formula: =NETWORKDAYS("10/10/2023", "10/25/2023")

    • Output: 12

30. Calculate the number of workdays excluding holidays:

    • Formula: =NETWORKDAYS("10/10/2023", "10/25/2023", {"10/12/2023", "10/19/2023"})

    • Output: 10

---

4. Advanced Date Functions

31. Convert a text string to a date:

    • Formula: =DATEVALUE("10/25/2023")

    • Output: 10/25/2023

32. Convert a date to a text string:

    • Formula: =TEXT("10/25/2023", "mmmm dd, yyyy")

    • Output: October 25, 2023

33. Extract the day name from a date:

    • Formula: =TEXT("10/25/2023", "dddd")

    • Output: Wednesday

34. Extract the month name from a date:

    • Formula: =TEXT("10/25/2023", "mmmm")

    • Output: October

35. Calculate the end of the current quarter:

    • Formula: =EOMONTH("10/25/2023", 3 - MONTH("10/25/2023") %% 3)

    • Output: 12/31/2023

36. Calculate the start of the next quarter:

    • Formula: =EOMONTH("10/25/2023", 3 - MONTH("10/25/2023") %% 3) + 1

    • Output: 01/01/2024

37. Calculate the number of days remaining in the month:

    • Formula: =EOMONTH("10/25/2023", 0) - "10/25/2023"

    • Output: 6

38. Calculate the number of days remaining in the year:

    • Formula: =DATE(YEAR("10/25/2023"), 12, 31) - "10/25/2023"

    • Output: 67

39. Calculate the number of days since the start of the year:

    • Formula: ="10/25/2023" - DATE(YEAR("10/25/2023"), 1, 1)

    • Output: 297

40. Calculate the number of days until a specific date:

    • Formula: ="12/31/2023" - "10/25/2023"

    • Output: 67

---

5. Conditional Date Functions

41. Check if a date is in the past:

    • Formula: =IF("10/25/2023" < TODAY(), "Past", "Future")

    • Output: Past (if today is October 26, 2023)

42. Check if a date is in the current month:

    • Formula: =IF(MONTH("10/25/2023") = MONTH(TODAY()), "Current Month", "Other Month")

    • Output: Current Month (if today is October 2023)

43. Check if a date is in the current year:

    • Formula: =IF(YEAR("10/25/2023") = YEAR(TODAY()), "Current Year", "Other Year")

    • Output: Current Year (if today is 2023)

44. Check if a date is a weekday:

    • Formula: =IF(WEEKDAY("10/25/2023", 2) < 6, "Weekday", "Weekend")

    • Output: Weekday

45. Check if a date is a holiday:

    • Formula: =IF(OR("10/25/2023" = "12/25/2023", "10/25/2023" = "01/01/2024"), "Holiday", "Not Holiday")

    • Output: Not Holiday

---

6. Date Formatting

46. Format a date as "YYYY-MM-DD":

    • Formula: =TEXT("10/25/2023", "yyyy-mm-dd")

    • Output: 2023-10-25

47. Format a date as "DD/MM/YYYY":

    • Formula: =TEXT("10/25/2023", "dd/mm/yyyy")

    • Output: 25/10/2023

48. Format a date as "Month DD, YYYY":

    • Formula: =TEXT("10/25/2023", "mmmm dd, yyyy")

    • Output: October 25, 2023

49. Format a date as "Day, Month DD, YYYY":

    • Formula: =TEXT("10/25/2023", "dddd, mmmm dd, yyyy")

    • Output: Wednesday, October 25, 2023

50. Format a date as "YYYYMMDD":

    • Formula: =TEXT("10/25/2023", "yyyymmdd")

    • Output: 20231025

---

7. Complex Nested Date Functions

51. Calculate the last Friday of the month:

    • Formula: =EOMONTH("10/25/2023", 0) - WEEKDAY(EOMONTH("10/25/2023", 0) + 1) + 6

    • Output: 10/27/2023

52. Calculate the first Monday of the month:

    • Formula: =DATE(YEAR("10/25/2023"), MONTH("10/25/2023"), 1) + MOD(8 - WEEKDAY(DATE(YEAR("10/25/2023"), MONTH("10/25/2023"), 1)), 7)

    • Output: 10/02/2023

53. Calculate the number of Sundays in a month:

    • Formula: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR("10/25/2023"), MONTH("10/25/2023"), 1) & ":" & EOMONTH("10/25/2023", 0))))=1))

    • Output: 4

54. Calculate the number of weekdays in a month:

    • Formula: =NETWORKDAYS(DATE(YEAR("10/25/2023"), MONTH("10/25/2023"), 1), EOMONTH("10/25/2023", 0))

    • Output: 22

55. Calculate the number of weekends in a month:

    • Formula: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR("10/25/2023"), MONTH("10/25/2023"), 1) & ":" & EOMONTH("10/25/2023", 0))))=1)) + SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR("10/25/2023"), MONTH("10/25/2023"), 1) & ":" & EOMONTH("10/25/2023", 0))))=7))

    • Output: 8

---

8. Date Functions with Time

56. Extract the time from a datetime:

    • Formula: =MOD("10/25/2023 14:30", 1)

    • Output: 0.604166667 (2:30 PM)

57. Add hours to a datetime:

    • Formula: ="10/25/2023 14:30" + TIME(3, 0, 0)

    • Output: 10/25/2023 17:30

58. Subtract hours from a datetime:

    • Formula: ="10/25/2023 14:30" - TIME(2, 0, 0)

    • Output: 10/25/2023 12:30

59. Calculate the difference in hours between two datetimes:

    • Formula: =("10/25/2023 17:30" - "10/25/2023 14:30") * 24

    • Output: 3

60. Calculate the difference in minutes between two datetimes:

    • Formula: =("10/25/2023 17:30" - "10/25/2023 14:30") * 1440

    • Output: 180

---

9. Date Functions with Arrays

61. Create a list of dates for a month:

    • Formula: =SEQUENCE(31, 1, DATE(2023, 10, 1), 1)

    • Output: 10/01/2023, 10/02/2023, ..., 10/31/2023

62. Create a list of weekdays for a month:

    • Formula: =FILTER(SEQUENCE(31, 1, DATE(2023, 10, 1), 1), WEEKDAY(SEQUENCE(31, 1, DATE(2023, 10, 1), 1), 2) < 6)

    • Output: 10/02/2023, 10/03/2023, ..., 10/31/2023 (excluding weekends)

63. Create a list of weekends for a month:

    • Formula: =FILTER(SEQUENCE(31, 1, DATE(2023, 10, 1), 1), WEEKDAY(SEQUENCE(31, 1, DATE(2023, 10, 1), 1), 2) > 5)

    • Output: 10/01/2023, 10/07/2023, ..., 10/29/2023

64. Create a list of dates for the next 7 days:

    • Formula: =SEQUENCE(7, 1, TODAY(), 1)

    • Output: 10/25/2023, 10/26/2023, ..., 10/31/2023

65. Create a list of dates for the previous 7 days:

    • Formula: =SEQUENCE(7, 1, TODAY() - 7, 1)

    • Output: 10/18/2023, 10/19/2023, ..., 10/24/2023

---

10. Date Functions with Logical Conditions

66. Check if a date is within a range:

    • Formula: =IF(AND("10/25/2023" >= "10/01/2023", "10/25/2023" <= "10/31/2023"), "Within Range", "Out of Range")

    • Output: Within Range

67. Check if a date is a leap year:

    • Formula: =IF(MONTH(DATE(YEAR("10/25/2023"), 2, 29))=2, "Leap Year", "Not Leap Year")

    • Output: Not Leap Year

68. Check if a date is the last day of the month:

    • Formula: =IF("10/25/2023" = EOMONTH("10/25/2023", 0), "Last Day", "Not Last Day")

    • Output: Not Last Day

69. Check if a date is the first day of the month:

    • Formula: =IF("10/25/2023" = DATE(YEAR("10/25/2023"), MONTH("10/25/2023"), 1), "First Day", "Not First Day")

    • Output: Not First Day

70. Check if a date is a specific day of the week:

    • Formula: =IF(WEEKDAY("10/25/2023") = 4, "Wednesday", "Not Wednesday")

    • Output: Wednesday

---

11. Date Functions with Lookups

71. Find the closest date in a list:

    • Formula: =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10 - "10/25/2023")), ABS(A1:A10 - "10/25/2023"), 0))

    • Output: 10/24/2023 (if 10/24/2023 is the closest date in the list)

72. Find the latest date in a list:

    • Formula: =MAX(A1:A10)

    • Output: 10/31/2023 (if 10/31/2023 is the latest date in the list)

73. Find the earliest date in a list:

    • Formula: =MIN(A1:A10)

    • Output: 10/01/2023 (if 10/01/2023 is the earliest date in the list)

74. Find the date with the highest value in another column:

    • Formula: =INDEX(A1:A10, MATCH(MAX(B1:B10), B1:B10, 0))

    • Output: 10/25/2023 (if 10/25/2023 has the highest value in column B)

75. Find the date with the lowest value in another column:

    • Formula: =INDEX(A1:A10, MATCH(MIN(B1:B10), B1:B10, 0))

    • Output: 10/01/2023 (if 10/01/2023 has the lowest value in column B)

---

12. Date Functions with Aggregations

76. Count the number of dates in a list:

    • Formula: =COUNT(A1:A10)

    • Output: 10 (if there are 10 dates in the list)

77. Count the number of dates in a specific month:

    • Formula: =SUMPRODUCT(--(MONTH(A1:A10) = 10))

    • Output: 5 (if there are 5 dates in October)

78. Count the number of dates in a specific year:

    • Formula: =SUMPRODUCT(--(YEAR(A1:A10) = 2023))

    • Output: 10 (if all dates are in 2023)

79. Sum values for a specific month:

    • Formula: =SUMPRODUCT((MONTH(A1:A10) = 10) * B1:B10)

    • Output: 500 (if the sum of values in column B for October is 500)

80. Average values for a specific month:

    • Formula: =AVERAGEIF(A1:A10, ">=10/01/2023", B1:B10)

    • Output: 50 (if the average of values in column B for October is 50)

---

13. Date Functions with Conditional Formatting

81. Highlight dates in the past:

    • Formula: =A1 < TODAY()

    • Output: Highlights dates before today.

82. Highlight dates in the future:

    • Formula: =A1 > TODAY()

    • Output: Highlights dates after today.

83. Highlight weekends:

    • Formula: =OR(WEEKDAY(A1) = 1, WEEKDAY(A1) = 7)

    • Output: Highlights Saturdays and Sundays.

84. Highlight holidays:

    • Formula: =OR(A1 = "12/25/2023", A1 = "01/01/2024")

    • Output: Highlights specific holidays.

85. Highlight dates within a range:

    • Formula: =AND(A1 >= "10/01/2023", A1 <= "10/31/2023")

    • Output: Highlights dates in October 2023.

---

14. Date Functions with Text Manipulation

86. Extract the year from a date string:

    • Formula: =LEFT("10/25/2023", 4)

    • Output: 2023

87. Extract the month from a date string:

    • Formula: =MID("10/25/2023", 4, 2)

    • Output: 10

88. Extract the day from a date string:

    • Formula: =RIGHT("10/25/2023", 2)

    • Output: 25

89. Combine text and dates:

    • Formula: ="Today is " & TEXT(TODAY(), "mmmm dd, yyyy")

    • Output: Today is October 25, 2023

90. Convert a date string to a different format:

    • Formula: =TEXT("10/25/2023", "dd-mmm-yy")

    • Output: 25-Oct-23

---

15. Date Functions with Error Handling

91. Check if a date is valid:

    • Formula: =IF(ISDATE("10/25/2023"), "Valid", "Invalid")

    • Output: Valid

92. Handle invalid dates:

    • Formula: =IFERROR(DATEVALUE("13/25/2023"), "Invalid Date")

    • Output: Invalid Date

93. Check if a date is blank:

    • Formula: =IF(ISBLANK(A1), "Blank", "Not Blank")

    • Output: Blank (if A1 is empty)

94. Replace invalid dates with a default value:

    • Formula: =IF(ISDATE(A1), A1, "01/01/2023")

    • Output: 01/01/2023 (if A1 is invalid)

95. Check if a date is within a valid range:

    • Formula: =IF(AND(A1 >= "01/01/2023", A1 <= "12/31/2023"), "Valid", "Invalid")

    • Output: Valid (if A1 is within 2023)

---

16. Date Functions with Custom Logic

96. Calculate the next business day:

    • Formula: =WORKDAY("10/25/2023", 1)

    • Output: 10/26/2023

97. Calculate the previous business day:

    • Formula: =WORKDAY("10/25/2023", -1)

    • Output: 10/24/2023

98. Calculate the number of business days between two dates:

    • Formula: =NETWORKDAYS("10/10/2023", "10/25/2023")

    • Output: 12

99. Calculate the number of business days excluding holidays:

    • Formula: =NETWORKDAYS("10/10/2023", "10/25/2023", {"10/12/2023", "10/19/2023"})

    • Output: 10

100. Calculate the number of business days in a month:
     - Formula: =NETWORKDAYS(DATE(YEAR("10/25/2023"), MONTH("10/25/2023"), 1), EOMONTH("10/25/2023", 0))
     - Output: 22

---

17. Date Functions with Time Zones

101. Convert a datetime to UTC:
     - Formula: ="10/25/2023 14:30" - TIME(5, 0, 0)
     - Output: 10/25/2023 09:30 (if converting from EST to UTC)

102. Convert a datetime from UTC to local time:
     - Formula: ="10/25/2023 14:30" + TIME(5, 0, 0)
     - Output: 10/25/2023 19:30 (if converting from UTC to EST)

103. Calculate the time difference between two time zones:
     - Formula: =TIME(5, 0, 0) - TIME(3, 0, 0)
     - Output: 02:00

104. Adjust a datetime for daylight saving time:
     - Formula: ="10/25/2023 14:30" - TIME(1, 0, 0)
     - Output: 10/25/2023 13:30 (if adjusting for DST)

105. Calculate the current time in a different time zone:
     - Formula: =NOW() + TIME(5, 0, 0)
     - Output: 10/25/2023 19:30 (if converting from UTC to EST)

---

18. Date Functions with Financial Calculations

106. Calculate the number of days between two dates for interest calculation:
     - Formula: ="10/25/2023" - "10/10/2023"
     - Output: 15

107. Calculate the number of days in a year for interest calculation:
     - Formula: =DATE(YEAR("10/25/2023"), 12, 31) - DATE(YEAR("10/25/2023"), 1, 1)
     - Output: 364

108. Calculate the number of days in a month for interest calculation:
     - Formula: =EOMONTH("10/25/2023", 0) - DATE(YEAR("10/25/2023"), MONTH("10/25/2023"), 1)
     - Output: 30

109. Calculate the number of days between two dates for bond pricing:
     - Formula: ="10/25/2023" - "10/10/2023"
     - Output: 15

110. Calculate the number of days in a quarter for financial reporting:
     - Formula: =EOMONTH("10/25/2023", 3 - MONTH("10/25/2023") %% 3) - DATE(YEAR("10/25/2023"), MONTH("10/25/2023") - MOD(MONTH("10/25/2023") - 1, 3), 1)
     - Output: 92

---

19. Date Functions with Project Management

111. Calculate the end date of a project:
     - Formula: ="10/25/2023" + 30
     - Output: 11/24/2023

112. Calculate the start date of a project:
     - Formula: ="10/25/2023" - 30
     - Output: 09/25/2023

113. Calculate the number of workdays in a project:
     - Formula: =NETWORKDAYS("10/25/2023", "11/24/2023")
     - Output: 23

114. Calculate the number of workdays excluding holidays:
     - Formula: =NETWORKDAYS("10/25/2023", "11/24/2023", {"11/11/2023", "11/23/2023"})
     - Output: 21

115. Calculate the number of weekends in a project:
     - Formula: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT("10/25/2023" & ":" & "11/24/2023"))) > 5))
     - Output: 8

---

20. Date Functions with Data Validation

116. Restrict input to dates in the future:
     - Formula: =A1 > TODAY()
     - Output: Allows only future dates.

117. Restrict input to dates in the past:
     - Formula: =A1 < TODAY()
     - Output: Allows only past dates.

118. Restrict input to dates within a specific range:
     - Formula: =AND(A1 >= "10/01/2023", A1 <= "10/31/2023")
     - Output: Allows only dates in October 2023.

119. Restrict input to weekdays:
     - Formula: =WEEKDAY(A1, 2) < 6
     - Output: Allows only weekdays.

120. Restrict input to specific holidays:
     - Formula: =NOT(OR(A1 = "12/25/2023", A1 = "01/01/2024"))
     - Output: Allows only non-holiday dates.

---

21. Date Functions with Pivot Tables

121. Group dates by month in a pivot table:
     - Formula: =TEXT(A1, "mmmm")
     - Output: October

122. Group dates by quarter in a pivot table:
     - Formula: ="Q" & ROUNDUP(MONTH(A1)/3, 0)
     - Output: Q4

123. Group dates by year in a pivot table:
     - Formula: =YEAR(A1)
     - Output: 2023

124. Group dates by week in a pivot table:
     - Formula: =WEEKNUM(A1)
     - Output: 43

125. Group dates by day of the week in a pivot table:
     - Formula: =TEXT(A1, "dddd")
     - Output: Wednesday

---

22. Date Functions with Charts

126. Create a line chart with dates on the x-axis:
     - Formula: =A1:A10 (dates) and =B1:B10 (values)
     - Output: A line chart with dates on the x-axis.

127. Create a bar chart with dates on the x-axis:
     - Formula: =A1:A10 (dates) and =B1:B10 (values)
     - Output: A bar chart with dates on the x-axis.

128. Create a scatter plot with dates on the x-axis:
     - Formula: =A1:A10 (dates) and =B1:B10 (values)
     - Output: A scatter plot with dates on the x-axis.

129. Create a histogram with dates:
     - Formula: =A1:A10 (dates)
     - Output: A histogram with dates.

130. Create a Gantt chart with dates:
     - Formula: =A1:A10 (start dates) and =B1:B10 (end dates)
     - Output: A Gantt chart.

---

23. Date Functions with Macros

131. Automate date entry with a macro:
     - Formula: =TODAY()
     - Output: Automatically enters today's date.

132. Automate date formatting with a macro:
     - Formula: =TEXT(A1, "mmmm dd, yyyy")
     - Output: Automatically formats dates.

133. Automate date calculations with a macro:
     - Formula: =A1 + 7
     - Output: Automatically adds 7 days to a date.

134. Automate date validation with a macro:
     - Formula: =IF(A1 < TODAY(), "Past", "Future")
     - Output: Automatically validates dates.

135. Automate date filtering with a macro:
     - Formula: =FILTER(A1:A10, A1:A10 >= "10/01/2023")
     - Output: Automatically filters dates.

---

24. Date Functions with External Data

136. Import dates from a CSV file:
     - Formula: =IMPORTDATA("dates.csv")
     - Output: Imports dates from a CSV file.

137. Import dates from a database:
     - Formula: =QUERY("SELECT date FROM table", "where date >= '10/01/2023'")
     - Output: Imports dates from a database.

138. Import dates from a web page:
     - Formula: =IMPORTHTML("https://example.com", "table", 1)
     - Output: Imports dates from a web page.

139. Import dates from an API:
     - Formula: =IMPORTJSON("https://api.example.com/dates", "/dates")
     - Output: Imports dates from an API.

140. Import dates from Google Sheets:
     - Formula: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/12345", "Sheet1!A1:A10")
     - Output: Imports dates from another Google Sheet.

---

These examples cover a wide range of date-related tasks, from basic calculations to advanced manipulations and integrations