Spreadsheet Date Manipulation

Applications

Office Productivity

Spreadsheets

Date Functions

SS Date Functions

Current Date

=TODAY()

DateTime Functions

Current Date and Time

=NOW()

Day Functions

MonthDay Number of Date

=DAY(B2)

WeekDay Number of Date

Assuming you have a date (format mask irrelevant) in F5 you can do this to show the day number in another cell...

=WEEKDAY(F5)

By default, Sunday is day 1 and Saturday is day 7Note that formatting the cell with a ddd format mask, will convert the number to an abbreviated day name (Mon through to Sat)

nth Day (of Month)

First Sunday

=$B$5-DAY($B$5)+8-WEEKDAY($B$5-DAY($B$5)+7)

First Monday

=$B$5-DAY($B$5)+8-WEEKDAY($B$5-DAY($B$5)+6)

First Tuesday

=$B$5-DAY($B$5)+8-WEEKDAY($B$5-DAY($B$5)+5)

First Wednesday

=$B$5-DAY($B$5)+8-WEEKDAY($B$5-DAY($B$5)+4)

First Thursday

=$B$5-DAY($B$5)+8-WEEKDAY($B$5-DAY($B$5)+3)

First Friday

=$B$5-DAY($B$5)+8-WEEKDAY($B$5-DAY($B$5)+2)

First Saturday

=$B$5-DAY($B$5)+8-WEEKDAY($B$5-DAY($B$5)+1)

Second Sunday

=$B$5-DAY($B$5)+8-WEEKDAY($B$5-DAY($B$5)+7)+7

Second Saturday

=$B$5-DAY($B$5)+8-WEEKDAY($B$5-DAY($B$5)+1)+7

Third Sunday

=$B$5-DAY($B$5)+8-WEEKDAY($B$5-DAY($B$5)+7)+14

Third Saturday

=$B$5-DAY($B$5)+8-WEEKDAY($B$5-DAY($B$5)+1)+14

Fourth Sunday

=$B$5-DAY($B$5)+8-WEEKDAY($B$5-DAY($B$5)+7)+21

Fourth Saturday

=$B$5-DAY($B$5)+8-WEEKDAY($B$5-DAY($B$5)+1)+21

Where $B$5 contains the month e.g.Mar 2022
If you format the cell as ddd dd-mon-yyyyIt will show this for First Sunday...
Sun 06-Mar-2022

The value in $B$5 is assumed to be equivalent to the 1st day of the target month. i.e. if you put Mar-2022 in a cell and reformat it to dd-mon-yyyy, you should see 01-Mar-2022 (the 01 is assumed).

Parameter driven

=$B$5-DAY($B$5)+8-WEEKDAY($B$5-DAY($B$5)+(8-P27))+((O27*7)-7)

Where $B$5 contains the month e.g.Mar 2022P27 contains the occurrence e.g.3O27 contains the day (1=Sun, 7=Sat) e.g.4Which yields the 3rd Wed of Mar-2022Wed 16-Mar-2022

SS Date Functions

Week Functions

Week Number of date

Assuming you have a date (format mask irrelevant) in F5 you can do this to show a labelled week number in another cell...

="Week "&WEEKNUM(B2)

By default WEEKNUM assumes Sunday to be the first day of each week.The & joins the literal string to the function result.

This is the formula in D1 in the example above

=ISOWEEKNUM(B2)

Month Functions

Month Number of Date

=MONTH(B2)

End of month containing date in B2...

=EOMONTH(B2,0)

End of month after month of date in B2

=EOMONTH(B2,1)

Year Functions

Year Number of Date

=YEAR(B2)

Working Hours

To calculate the number of working hours between two dates (assuming a 7.5 hour working day)...

=VALUE(NETWORKDAYS(F5,AZ5))*7.5

Assumes F5 holds the start date and AZ5 holds the end date

By setting up a sheet to act as a holiday lookup table you can ensure national holidays are also ignored...

=NETWORKDAYS.INTL(EOMONTH($U$5,-1), EOMONTH($U$5,0), 1, 'UK Holidays'!$A$2:$A$9)

The "UK Holidays" lookup sheet has two columns: "Date" and "Holiday"EOMONTH returns the last day of the month... this example is working out working days between the end of previous month and the end of the month specified in cell $U$5

UK Bank Holidays

To get working hours for the current month, use something like this...

=VALUE(NETWORKDAYS.INTL(EOMONTH($G$1,-1),EOMONTH($G$1,0),1,'UK Holidays'!$A$2:$A$9))*7.5

Where G1 contains the date of the first day of the month and a working day is assumed to be 7.5 hours

Date Maths

SS Date Functions

Adding/Subtracting Months

Add one month to the date in B18...

=EDATE($B18,1)

Subtract one month from the date in B18...

=EDATE($B18,-1)

Number of Days between Dates

=DAYS(B2,DATE(2023, 01, 31))

TODO

=DAYS360(B2,DATE(2023, 01, 31),1)

=DATEDIF(B2,DATE(2023, 01, 31), "D")

=DATEDIF(B2,DATE(2023, 01, 31), "MD")

Number of days between the two dates, where whole years and months are subtracted.

=DATEDIF(B2,DATE(2023, 01, 31), "YD")

Number of days between the two dates, where the two dates are considered no more than a year apart.

Number of Months between Dates

=DATEDIF(B2,DATE(2024, 01, 31), "M")

Whole months only

=DATEDIF(B2,DATE(2024, 01, 31), "YM")

Number of whole months between the two dates, where whole years are subtracted.

Add Working Days to a Date

=WORKDAY(B2,10,'UK Holidays'!$A$2:$A$9)

=WORKDAY.INTL(B2,10,1,'UK Holidays'!$A$2:$A$9)

Number of Years between Dates

=YEARFRAC(B2, DATE(2023, 01, 31), 1)

Includes fractional years

=DATEDIF(B2,DATE(2024, 01, 31), "Y")

Whole years only

Date Conversion

Date Value

=DATEVALUE(B2)

Convert Year, Month, Day to Date

=DATE(2023, 01, 31)

Bibliography

https://infoinspired.com/google-docs/spreadsheet/google-sheets-date-functions-complete-guide/https://infoinspired.com/google-docs/spreadsheet/date-related-conditional-formatting/https://www.howtogeek.com/439209/how-to-highlight-a-row-in-google-sheets-using-conditional-formatting/https://corporatefinanceinstitute.com/resources/excel/excel-text-function/https://support.microsoft.com/en-au/office/calculate-the-difference-between-two-times-e1c78778-749b-49a3-b13e-737715505ff6https://excelchamps.com/formulas/seconds-to-hours-minuteshttps://trumpexcel.com/remove-time-from-date-in-excel/
WEEKDAYhttps://exceljet.net/excel-functions/excel-weekday-function
WEEKNUMhttps://www.wallstreetmojo.com/week-number-in-excel/
NETWORKDAYShttps://exceljet.net/formula/get-workdays-between-dates
EOMONTHhttps://exceljet.net/excel-functions/excel-eomonth-functionhttps://www.statology.org/google-sheets-first-day-of-month/
EDATEhttps://www.statology.org/google-sheets-add-months-to-date/https://support.google.com/docs/answer/3092974?hl=enhttps://support.microsoft.com/en-us/office/edate-function-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5
DAYhttps://www.exceldome.com/solutions/excel-day-function/https://www.statology.org/google-sheets-first-day-of-month/
DATEDIFhttps://www.wikihow.com/Calculate-Number-of-Days-Between-Two-Dates-in-Excelhttps://www.makeuseof.com/how-to-calculate-difference-between-dates-google-sheets/
nth Dayhttps://www.exceldome.com/solutions/return-first-monday-of-a-month/https://office-watch.com/2022/ordinal-numbers-excel-1st-2nd-3rd-etc/https://www.mrexcel.com/excel-tips/find-the-last-sunday-of-the-month/https://excelchamps.com/formulas/previous-sunday
Handling Blank Dateshttps://www.excelforum.com/excel-formulas-and-functions/1037781-vlookup-returns-1-0-1900-a.htmlhttps://superuser.com/questions/1720156/dont-show-1900-on-blank-date

Page updated

Google Sites

Report abuse