Date calculation formula

aalligood

New Member
Joined
Feb 14, 2025
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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:


DateDay TypeReport Date
06/01/25Sunday
06/02/25Monday[05/31/2025.06/02/2025]
06/03/25Tuesday06/03/2025
06/04/25Wednesday06/04/2025
06/05/25Thursday06/05/2025
06/06/25Friday06/06/2025
06/07/25Saturday
06/08/25Sunday
06/09/25Monday[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.
 
Welcome to the Forum!

Perhaps something like this?

ABCD
1ReportReport
2DateSalesDateSales
3Fri 31 Jan 20258031 Jan80
4Sat 1 Feb 202565--
5Mon 3 Feb 2025801 Feb -3 Feb145
6Tue 4 Feb 2025954 Feb95
7Wed 5 Feb 2025605 Feb60
8Thu 6 Feb 2025956 Feb95
9Fri 7 Feb 2025957 Feb95
10Sat 8 Feb 202555--
11Sun 9 Feb 202570--
12Mon 10 Feb 2025958 Feb -10 Feb220
13Tue 11 Feb 202510011 Feb100
14Wed 12 Feb 20258512 Feb85
Sheet1
Cell Formulas
RangeFormula
C3:C14C3=IF(WEEKDAY(A3,2)>5,"-",IF(WEEKDAY(A3,2)=1,TEXT(A3-2,"d mmm -")&TEXT(A3,"d mmm"),A3))
D3:D14D3=IF(WEEKDAY(A3,2)>5,"-",IF(WEEKDAY(A3,2)=1,SUMIFS(B1:B3,A1:A3,">"&A3-3),B3))
 
Upvote 0
... 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?
 
Upvote 0
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.
 
Upvote 0
See if the following formula works for you:
Excel Formula:
=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))&"]")))
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top