Date calculation formula

[aalligood]

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:

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.

 

[StephenCrump]

Welcome to the Forum!

Perhaps something like this?

	A	B	C	D
1			Report	Report
2	Date	Sales	Date	Sales
3	Fri 31 Jan 2025	80	31 Jan	80
4	Sat 1 Feb 2025	65	-	-
5	Mon 3 Feb 2025	80	1 Feb -3 Feb	145
6	Tue 4 Feb 2025	95	4 Feb	95
7	Wed 5 Feb 2025	60	5 Feb	60
8	Thu 6 Feb 2025	95	6 Feb	95
9	Fri 7 Feb 2025	95	7 Feb	95
10	Sat 8 Feb 2025	55	-	-
11	Sun 9 Feb 2025	70	-	-
12	Mon 10 Feb 2025	95	8 Feb -10 Feb	220
13	Tue 11 Feb 2025	100	11 Feb	100
14	Wed 12 Feb 2025	85	12 Feb	85
Sheet1

Cell Formulas
Range	Formula
C3:C14	C3	=IF(WEEKDAY(A3,2)>5,"-",IF(WEEKDAY(A3,2)=1,TEXT(A3-2,"d mmm -")&TEXT(A3,"d mmm"),A3))
D3:D14	D3	=IF(WEEKDAY(A3,2)>5,"-",IF(WEEKDAY(A3,2)=1,SUMIFS(B1:B3,A1:A3,">"&A3-3),B3))

 

[Tetra201]

... 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...

And where do you want to report sales from, say, Saturday the 30th and Sunday the 31st?

 

[aalligood]

And where do you want to report sales from, say, Saturday the 30th and Sunday the 31st?

I had not considered that. I will need to find out how users want it to report. I'll keep you posted.

 

[aalligood]

And where do you want to report sales from, say, Saturday the 30th and Sunday the 31st?

We're trying to capture all billable sales for a specific month and push them into week days. If the weekend is the 30 or 31st, those sales should be pushed back to Friday.

 

[Tetra201]

See if the following formula works for you:

=LET(w,WEEKDAY(A10,2),e,EOMONTH(A10,0),f,"mm/dd/yyyy",IF(w>5,"",IF(OR(OR(w={2,3,4}),AND(w=1,DAY(A10)=1),AND(w=5,A10<e-2)),TEXT(A10,f),"["&TEXTJOIN(".",,TEXT(IF(w=1,CHOOSE({1,2},MAX(A10-2,EOMONTH(A10,-1)+1),A10),CHOOSE({1,2},A10,e)),f))&"]")))