I'm trying to create a formula that would allow me to figure out the value of booking within a month if it is for weekdays but doesn't overlap the weekend. So a guest may check in on a Tuesday and check out on a Friday and this would equate to "True" and the value of the booking would be counted as a weekday. So sum a range if a start date does not contain a Friday or Saturday, end date does not contain a Saturday or Sunday, and the date range does not contains/overlaps both a Saturday and Sunday.
G1=5/1/2022
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | start | end | value | house | weekday? |
| 2 | 5/9/2022 | 5/13/2022 | 250 | house 1 | true |
| 3 | 5/19/2022 | 5/21/2022 | 225 | house 1 | false |
| 4 | 5/29/2022 | 6/1/2022 | 175 | house 1 | true |
| 5 | 5/24/2022 | 6/1/2022 | 150 | house 2 | false |
=SUMIFS(C2:C4,A2:A5,">="&G1,C2:C4, "<="&EOMONTH(G1,0),D2:D5,"house 1", this is the part where I need to determine if the date range contains a start on a friday or saturday, ends on saturday or sunday, or the range contains both a friday and a saturday)
The end result would be that both A2 and A4 would be true and equate to A2 A4 = 250 150 = 400
Basically I am using historic booking data to forecast potential future revenue by looking what bookings per month were, breaking them down by if the booking contained a weekend or was just weekdays, and then looking at remaining open weekdays in the year and projecting potential revenue by what average number of dates were that were booked in low season and high season. This above is just step 1 in a much larger equation.
CodePudding user response：
If you put this function in E2 and drag it down, I think it populates properly with True/False values (note I previously overlooked the potential same-day checkout.
=AND(WEEKDAY(A2,1)<6,WEEKDAY(B2,2)<6,WEEKDAY(B2,1)>=WEEKDAY(A2,1),B2-A2<5)
Here's a dynamic spill version of the formula that updates as the column gets more data.
=FILTER(((WEEKDAY(A:A,1)<6)*(WEEKDAY(B:B,2)<6)*(WEEKDAY(B:B,1)>=WEEKDAY(A:A,1))*(B:B-A:A<5))=1,(A:A>0)*(ROW(A:A)>1))
I think it covers all possibilities. I threw in some more dates for testing....
CodePudding user response：
I put your data in a Table which I named Bookings.
Given the apparent change in your requirements as pointed out to me by @pgSystemTester:
The formula for the weekday? column would then be:
=AND(WEEKDAY([@start],17)<6,WEEKDAY(ROW(INDEX($A:$A,[@start] 1):INDEX($A:$A,[@end])),2)<6)
and the total:
=SUMIF(Bookings[Weekday?],TRUE,Bookings[value])