Automatically find start and end of the work week in a Google Spreadsheet

15
votes

My work week goes from Monday to Sunday and I need to find a way to have 2 different cells automatically update based on =today() what the actual date of the start and end of the current work week is.

Is there a formula that can find this?

Similarly:

Is there also a similar method to automate the first and last days of the current month in different cells?

Thank you ahead of time for any assistance

1
google-sheets

1 Answers

37
votes

Assuming you're referencing a date in A1...

Start of the work week containing that date (assuming work week starts on Monday):

=A1-WEEKDAY(A1;3)

End of the work week:

=A1-WEEKDAY(A1;3)+6

Start of the month:

=EOMONTH(A1;-1)+1

End of the month:

=EOMONTH(A1;0)

I know this is Microsoft Excel documentation, but this describes the EOMONTH function.