I have the data set (below a sample). Trying to get the number of nights, in each month
Asked
Active
Viewed 35 times
0
-
what about simple pivot table? or even simple `sum` row at the bottom? – NoobVB Sep 18 '22 at 08:27
-
Your data is pretty inconsistent. 6/15 -> 6/17 is 1 night (too few), 6/28 -> 6/29 is 1 night (correct) and 9/18 -> 9/24 is 6 nights (too many). – Phylogenesis Sep 18 '22 at 08:41
-
1@Phylogenesis I agree with your first two assessments, but not with the third. – Tom Brunberg Sep 18 '22 at 10:42
-
Use `NumOfDays = DateDiff("D", first_date, second_date)` – Tom Brunberg Sep 18 '22 at 10:51
1 Answers
0
I'm assuming that as the day of arrival goes with the first night, if someone arrived on 30th April and left on 1st May, that would count one night in April and zero nights in May. Using a slightly modified overlap formula:
=IF(MIN(EDATE(C$1,1),$B2)<=MAX(C$1,$A2),"",MIN(EDATE(C$1,1),$B2)-MAX(C$1,$A2))
assuming that a formula solution is OK.
Tom Sharpe
- 30,727
- 5
- 24
- 37