Hello!
I'm trying to calculate a report date for workdays for a financial sale report. I need a report date that is either the single date for a weekday or a date range that pulls in any sales from the weekend and combines them with Monday. The dates populate for the month based on TODAY().
I'm calculating whether the date is a weekday or weekend using =TEXT(cell, "dddd").
This is my nested IF formula that calculates the report date:
=IF(TEXT(C10,"dddd")="MONDAY",CONCAT("[",TEXT(A10-2,"MM/DD/YYYY"),".",TEXT(A10,"MM/DD/YYYY"),"]"),IF(TEXT(C10,"dddd")="TUESDAY",TEXT(A10,"MM/DD/YYYY"),IF(TEXT(C10,"dddd")="WEDNESDAY",TEXT(A10,"MM/DD/YYYY"),IF(TEXT(C10,"dddd")="THURSDAY",TEXT(A10,"MM/DD/YYYY"),IF(TEXT(C10,"dddd")="FRIDAY",TEXT(A10,"MM/DD/YYYY"),IF(TEXT(C10,"dddd")="SATURDAY","",IF(TEXT(C10,"dddd")="SUNDAY","","")))))))
Here's an example of the sheet:
As you can see, some months reach back and pull dates from the previous month. I can't seem to think through this to solve the issue.
Am I over complicating it?
Any assistance would be greatly appreciated.
I'm trying to calculate a report date for workdays for a financial sale report. I need a report date that is either the single date for a weekday or a date range that pulls in any sales from the weekend and combines them with Monday. The dates populate for the month based on TODAY().
I'm calculating whether the date is a weekday or weekend using =TEXT(cell, "dddd").
This is my nested IF formula that calculates the report date:
=IF(TEXT(C10,"dddd")="MONDAY",CONCAT("[",TEXT(A10-2,"MM/DD/YYYY"),".",TEXT(A10,"MM/DD/YYYY"),"]"),IF(TEXT(C10,"dddd")="TUESDAY",TEXT(A10,"MM/DD/YYYY"),IF(TEXT(C10,"dddd")="WEDNESDAY",TEXT(A10,"MM/DD/YYYY"),IF(TEXT(C10,"dddd")="THURSDAY",TEXT(A10,"MM/DD/YYYY"),IF(TEXT(C10,"dddd")="FRIDAY",TEXT(A10,"MM/DD/YYYY"),IF(TEXT(C10,"dddd")="SATURDAY","",IF(TEXT(C10,"dddd")="SUNDAY","","")))))))
Here's an example of the sheet:
Date | Day Type | Report Date |
06/01/25 | Sunday | |
06/02/25 | Monday | [05/31/2025.06/02/2025] |
06/03/25 | Tuesday | 06/03/2025 |
06/04/25 | Wednesday | 06/04/2025 |
06/05/25 | Thursday | 06/05/2025 |
06/06/25 | Friday | 06/06/2025 |
06/07/25 | Saturday | |
06/08/25 | Sunday | |
06/09/25 | Monday | [06/07/2025.06/09/2025] |
As you can see, some months reach back and pull dates from the previous month. I can't seem to think through this to solve the issue.
Am I over complicating it?
Any assistance would be greatly appreciated.