Number of days

M

Moo4247

New Member
Joined
Apr 24, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi evry1
Im conducting a mini suvery where need to put in number of days a visit lasted the marked yellow boxes.

Ive tries countifs, dateif and a few other formula, but none have worked so far... So when in doubt, Mr. excel preveils :D

1713949257311.png


your help is much needed
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
I'm not sure that I've calculated what you wanted calculated:
MrExcelPlayground22.xlsx
ABCDEF
1Visitdate4/24/2024
2112/23/20231/19/202427
322/19/20242/22/20243
432/25/20243/2/20246
543/15/20243/25/202410
653/28/20244/2/20245
764/6/20244/12/20246
874/15/20244/24/20249
9
10Number of days in the last month
114/24/20243/25/202420
12
13number of days last 2-3 months
143/24/20241/24/202419
15
16number of days last 4-5 months
171/23/202411/23/202327
18
19Number of visits in the last month
204/24/20243/25/20243
Sheet11
Cell Formulas
RangeFormula
D2:D8D2=C2-B2
B11B11=F1
C11,C20C11=EDATE(B11,-1)+1
E11,E17,E14E11=LET(x,$B$2:$B$8,y,$C$2:$C$8,a,SEQUENCE(B11-C11+1,1,C11),b,MAKEARRAY(ROWS(a),ROWS(x),LAMBDA(r,c,IF(INDEX(a,r)>=INDEX(x,c),1,0))),d,MAKEARRAY(ROWS(a),ROWS(y),LAMBDA(r,c,IF(INDEX(a,r)<INDEX(y,c),1,0))),e,b*d,f,SIGN(BYROW(e,LAMBDA(r,SUM(r)))),SUM(f))
B14,B17B14=C11-1
C14,C17C14=EDATE(B14,-2)
B20B20=F1
E20E20=LET(x,$B$2:$B$8,y,$C$2:$C$8,a,SEQUENCE(B20-C20+1,1,C20),b,MAKEARRAY(ROWS(a),ROWS(x),LAMBDA(r,c,IF(INDEX(a,r)>=INDEX(x,c),1,0))),d,MAKEARRAY(ROWS(a),ROWS(y),LAMBDA(r,c,IF(INDEX(a,r)<INDEX(y,c),1,0))),e,b*d,f,SIGN(BYCOL(e,LAMBDA(c,SUM(c)))),SUM(f))


You might have to tinker with < or <= to get just what you want.
 
Upvote 0
Hi Moo4247,

I am using excel2016, hence do not have full formula features from 365. But the way I will do this is by creating a few working sheets to help me record the days of visit.

1st sheet: "DataEntry": to do statistical analysis just like your example.
2nd sheet: "VisitDays": Full list of day to day, with Min&Max dates from entry, listing down all dates of Survey
3rd sheet: "DailyCount": Return "1" to the dates that is populated in "VisitDays"
Lastly, in 1st sheet, calculate all the days of visit (from range given), using SUMIF( ,<=,,>=) from "DailyCount"

Refer below:
20240424 total days from changing ranges date.xlsx
ABCD
1Survey Dates
2VisitCheck inCheck outDays Count
3123/12/202319/1/202428
4219/2/202422/2/20244
5325/2/20242/3/20247
6415/3/202425/3/202411
7528/3/20242/4/20246
866/4/202412/4/20247
9715/4/202424/4/202410
108 
119 
1210 
1311 
1412 
1513 
1614 
1715 
1816 
1917 
2018 
2119 
2220 
2321 
2422 
2523 
2624 
2725 
2826 
2927 
3028 
3129 
3230 
DataEntry
Cell Formulas
RangeFormula
D3:D32D3=IF(B3="","",C3-B3+1)


20240424 total days from changing ranges date.xlsx
ABCDEFGHIJK
1Visit12345678910
2MIN23/12/202319/2/202425/2/202415/3/202428/3/20246/4/202415/4/20240/1/19000/1/19000/1/1900
3MAX19/1/202422/2/20242/3/202425/3/20242/4/202412/4/202424/4/20240/1/19000/1/19000/1/1900
4Lists of dates23/12/202319/2/202425/2/202415/3/202428/3/20246/4/202415/4/20240/1/19000/1/19000/1/1900
524/12/202320/2/202426/2/202416/3/202429/3/20247/4/202416/4/2024   
625/12/202321/2/202427/2/202417/3/202430/3/20248/4/202417/4/2024   
726/12/202322/2/202428/2/202418/3/202431/3/20249/4/202418/4/2024   
827/12/2023 29/2/202419/3/20241/4/202410/4/202419/4/2024   
928/12/2023 1/3/202420/3/20242/4/202411/4/202420/4/2024   
1029/12/2023 2/3/202421/3/2024 12/4/202421/4/2024   
1130/12/2023  22/3/2024  22/4/2024   
1231/12/2023  23/3/2024  23/4/2024   
131/1/2024  24/3/2024  24/4/2024   
142/1/2024  25/3/2024      
153/1/2024         
164/1/2024         
175/1/2024         
186/1/2024         
197/1/2024         
208/1/2024         
219/1/2024         
2210/1/2024         
2311/1/2024         
2412/1/2024         
2513/1/2024         
2614/1/2024         
2715/1/2024         
2816/1/2024         
2917/1/2024         
3018/1/2024         
3119/1/2024         
32          
33          
34          
35          
36          
VisitDays
Cell Formulas
RangeFormula
B2:K3B2=TRANSPOSE(DataEntry!$B$3:$C$32)
B4:K4B4=IF(B2="0","",B2)
B5:K36B5=IFERROR(IF(B$2="0","",IF(B4+1>B$3,"",B4+1)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.


20240424 total days from changing ranges date.xlsx
AB
1DateCountif
223/12/20231
324/12/20231
425/12/20231
526/12/20231
627/12/20231
728/12/20231
829/12/20231
930/12/20231
1031/12/20231
111/1/20241
122/1/20241
133/1/20241
144/1/20241
155/1/20241
166/1/20241
177/1/20241
188/1/20241
199/1/20241
2010/1/20241
2111/1/20241
2212/1/20241
2313/1/20241
2414/1/20241
2515/1/20241
2616/1/20241
2717/1/20241
2818/1/20241
2919/1/20241
3020/1/20240
3121/1/20240
3222/1/20240
3323/1/20240
3424/1/20240
3525/1/20240
3626/1/20240
3727/1/20240
3828/1/20240
3929/1/20240
4030/1/20240
4131/1/20240
421/2/20240
432/2/20240
443/2/20240
454/2/20240
465/2/20240
476/2/20240
487/2/20240
498/2/20240
509/2/20240
5110/2/20240
5211/2/20240
5312/2/20240
5413/2/20240
5514/2/20240
5615/2/20240
5716/2/20240
5817/2/20240
5918/2/20240
6019/2/20241
6120/2/20241
6221/2/20241
6322/2/20241
6423/2/20240
6524/2/20240
6625/2/20241
6726/2/20241
6827/2/20241
6928/2/20241
7029/2/20241
711/3/20241
722/3/20241
733/3/20240
744/3/20240
755/3/20240
766/3/20240
777/3/20240
788/3/20240
799/3/20240
8010/3/20240
8111/3/20240
8212/3/20240
8313/3/20240
8414/3/20240
8515/3/20241
8616/3/20241
8717/3/20241
8818/3/20241
8919/3/20241
9020/3/20241
9121/3/20241
9222/3/20241
9323/3/20241
9424/3/20241
9525/3/20241
9626/3/20240
9727/3/20240
9828/3/20241
9929/3/20241
10030/3/20241
10131/3/20241
1021/4/20241
1032/4/20241
1043/4/20240
1054/4/20240
1065/4/20240
1076/4/20241
1087/4/20241
1098/4/20241
1109/4/20241
11110/4/20241
11211/4/20241
11312/4/20241
11413/4/20240
11514/4/20240
11615/4/20241
11716/4/20241
11817/4/20241
11918/4/20241
12019/4/20241
12120/4/20241
12221/4/20241
12322/4/20241
12423/4/20241
12524/4/20241
12625/4/20240
12726/4/20240
12827/4/20240
12928/4/20240
13029/4/20240
13130/4/20240
1321/5/20240
1332/5/20240
1343/5/20240
1354/5/20240
1365/5/20240
1376/5/20240
1387/5/20240
1398/5/20240
1409/5/20240
14110/5/20240
14211/5/20240
14312/5/20240
14413/5/20240
14514/5/20240
14615/5/20240
14716/5/20240
14817/5/20240
14918/5/20240
15019/5/20240
15120/5/20240
15221/5/20240
15322/5/20240
15423/5/20240
15524/5/20240
15625/5/20240
15726/5/20240
15827/5/20240
15928/5/20240
16029/5/20240
16130/5/20240
16231/5/20240
1631/6/20240
1642/6/20240
1653/6/20240
1664/6/20240
1675/6/20240
1686/6/20240
1697/6/20240
1708/6/20240
1719/6/20240
17210/6/20240
17311/6/20240
17412/6/20240
17513/6/20240
17614/6/20240
17715/6/20240
17816/6/20240
17917/6/20240
18018/6/20240
18119/6/20240
18220/6/20240
18321/6/20240
18422/6/20240
18523/6/20240
18624/6/20240
18725/6/20240
18826/6/20240
18927/6/20240
19028/6/20240
19129/6/20240
19230/6/20240
DailyCount
Cell Formulas
RangeFormula
A2A2=MIN(DataEntry!B3:C9)
B2:B192B2=COUNTIF(VisitDays!$B$4:$AE$64,A2)
A3:A192A3=A2+1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B:BCell Value=1textNO


20240424 total days from changing ranges date.xlsx
FGHIJ
1Range of DatesFormula calc.Manual calc.
2MaxMinTotal daysVisitVisit
324/4/202425/3/2024312424
424/3/202424/1/2024612121
523/1/202424/11/2023612828
624/4/202425/3/2024312424
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
DataEntry
Cell Formulas
RangeFormula
H3:H32H3=IF(F3="","",F3-G3+1)
I3:I32I3=IF(F3="","",SUMIFS(DailyCount!B:B,DailyCount!A:A,"<="&F3,DailyCount!A:A,">="&G3))
J3,J6J3=10+7+6+1
J4J4=4+7+10
J5J5=28


Remarks:
My date calculation will be having "+1", just to include the first day. You can remove this "+1" in the formulas.
I have allocated yellow cells for future entry.
Also, having manual calculation just to double check my formula in the SUMIFS (you can ignore this)
 
Upvote 0
JamesCanale said:
I'm not sure that I've calculated what you wanted calculated:
MrExcelPlayground22.xlsx
ABCDEF
1Visitdate4/24/2024
2112/23/20231/19/202427
322/19/20242/22/20243
432/25/20243/2/20246
543/15/20243/25/202410
653/28/20244/2/20245
764/6/20244/12/20246
874/15/20244/24/20249
9
10Number of days in the last month
114/24/20243/25/202420
12
13number of days last 2-3 months
143/24/20241/24/202419
15
16number of days last 4-5 months
171/23/202411/23/202327
18
19Number of visits in the last month
204/24/20243/25/20243
Sheet11
Cell Formulas
RangeFormula
D2:D8D2=C2-B2
B11B11=F1
C11,C20C11=EDATE(B11,-1)+1
E11,E17,E14E11=LET(x,$B$2:$B$8,y,$C$2:$C$8,a,SEQUENCE(B11-C11+1,1,C11),b,MAKEARRAY(ROWS(a),ROWS(x),LAMBDA(r,c,IF(INDEX(a,r)>=INDEX(x,c),1,0))),d,MAKEARRAY(ROWS(a),ROWS(y),LAMBDA(r,c,IF(INDEX(a,r)<INDEX(y,c),1,0))),e,b*d,f,SIGN(BYROW(e,LAMBDA(r,SUM(r)))),SUM(f))
B14,B17B14=C11-1
C14,C17C14=EDATE(B14,-2)
B20B20=F1
E20E20=LET(x,$B$2:$B$8,y,$C$2:$C$8,a,SEQUENCE(B20-C20+1,1,C20),b,MAKEARRAY(ROWS(a),ROWS(x),LAMBDA(r,c,IF(INDEX(a,r)>=INDEX(x,c),1,0))),d,MAKEARRAY(ROWS(a),ROWS(y),LAMBDA(r,c,IF(INDEX(a,r)<INDEX(y,c),1,0))),e,b*d,f,SIGN(BYCOL(e,LAMBDA(c,SUM(c)))),SUM(f))


You might have to tinker with < or <= to get just what you want.
Click to expand...

Hi James,

Thanks for your help - much needed :) - i'll try your formulas - i have not used =LET, lambda, and index all in one string :)

i'll diffinatly do some tweaks and exploring.

Thanks.
 
Upvote 0
Here are my formulas spread out a bit - so it makes it easier to see whats going on.

MrExcelPlayground22.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
1Visitdate4/24/2024
2112/23/20231/19/202427
322/19/20242/22/20243
432/25/20243/2/20246
543/15/20243/25/202410
653/28/20244/2/20245
764/6/20244/12/20246
874/15/20244/24/20249
9
10Number of days in the last month
114/24/20243/25/20242045376111100000001110000000020
12453771111000000011100000000
13number of days last 2-3 months453781111000000011100000000
143/24/20241/24/202419453791111100000011100001001
15453801111100000011100001001
16number of days last 4-5 months453811111100000011100001001
171/23/202411/23/202327453821111100000011100001001
18453831111100000011100001001
19Number of visits in the last month453841111100000001100000000
204/24/20243/25/20243453851111100000001100000000
21453861111100000001100000000
22453871111100000001100000000
23453881111110000001100000101
24453891111110000001100000101
25453901111110000001100000101
26453911111110000001100000101
27453921111110000001100000101
28453931111110000001100000101
29453941111110000000100000000
30453951111110000000100000000
31453961111110000000100000000
32453971111111000000100000011
33453981111111000000100000011
34453991111111000000100000011
35454001111111000000100000011
36454011111111000000100000011
37454021111111000000100000011
38454031111111000000100000011
39454041111111000000100000011
40454051111111000000100000011
41454061111111000000000000000
Sheet11
Cell Formulas
RangeFormula
D2:D8D2=C2-B2
B11B11=F1
C11,C20C11=EDATE(B11,-1)+1
E11,E17,E14E11=LET(x,$B$2:$B$8,y,$C$2:$C$8,a,SEQUENCE(B11-C11+1,1,C11),b,MAKEARRAY(ROWS(a),ROWS(x),LAMBDA(r,c,IF(INDEX(a,r)>=INDEX(x,c),1,0))),d,MAKEARRAY(ROWS(a),ROWS(y),LAMBDA(r,c,IF(INDEX(a,r)<INDEX(y,c),1,0))),e,b*d,f,SIGN(BYROW(e,LAMBDA(r,SUM(r)))),SUM(f))
G11:G41G11=LET(x,B2:B8,y,C2:C8,a,SEQUENCE(B11-C11+1,1,C11),a)
I11:O41I11=MAKEARRAY(ROWS(G11#),ROWS(B2:B8),LAMBDA(r,c,IF(INDEX(G11#,r)>=INDEX(B2:B8,c),1,0)))
Q11:W41Q11=MAKEARRAY(ROWS(G11#),ROWS(C2:C8),LAMBDA(r,c,IF(INDEX(G11#,r)<INDEX(C2:C8,c),1,0)))
Y11:AE41Y11=I11#*Q11#
AG11:AG41AG11=SIGN(BYROW(Y11#,LAMBDA(r,SUM(r))))
AI11AI11=SUM(AG11#)
B14,B17B14=C11-1
C14,C17C14=EDATE(B14,-2)
B20B20=F1
E20E20=LET(x,$B$2:$B$8,y,$C$2:$C$8,a,SEQUENCE(B20-C20+1,1,C20),b,MAKEARRAY(ROWS(a),ROWS(x),LAMBDA(r,c,IF(INDEX(a,r)>=INDEX(x,c),1,0))),d,MAKEARRAY(ROWS(a),ROWS(y),LAMBDA(r,c,IF(INDEX(a,r)<INDEX(y,c),1,0))),e,b*d,f,SIGN(BYCOL(e,LAMBDA(c,SUM(c)))),SUM(f))
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA Help- Need to create VBA Function from worksheet
Replies
9
Views
461
scotty1978
S
MrDB4Excel
  • Solved
Disregard Negative Results
Replies
3
Views
88
Joe4
Joe4
O
  • Solved
Counting instances of unique entries, with conditions
Replies
1
Views
402
ollyhughes1982
O
C
  • Solved
Number of days between dates
Replies
5
Views
787
Dave Patton
D
A
  • Solved
Fill down formulas and Do Until "No more data"
Replies
4
Views
770
felixstraube
F

Forum statistics

Threads
1,215,432
Messages
6,124,856
Members
449,194
Latest member
HellScout

Share this page

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