Count of unique Dates in range by Month and year

PrachiOU

New Member
Joined
Apr 1, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
I have a hourly date range for a year, I am trying to find the latest month with the data of whole month:
For eg i have data from 3rd Jan 2020 to 5th Feb 2021 and I need the latest month as Jan 2021

Is there as way to do this in excel.
 

Attachments

  • test.PNG
    test.PNG
    38.8 KB · Views: 4

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

What if you had data to 28 Feb 2021?
Would we have to check
- to see if the latest time was, say 11:00 PM for the answer to be Feb 2021 or
- would the answer already be decided as Feb 2021 or
- Jan 2021

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
 

PrachiOU

New Member
Joined
Apr 1, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Thankyou for replying Peter_SSs,
In my file I have daily data of each hour(so it will have whole 24hours data for 28). if the latest time was11:00 PM for the answer to be Feb 2021 it should select Feb 2021
Else for any other day in Feb like 27th, 15th etc, it should select Jan 2021.

Adding only the data of Jan and Feb 2021 date and hour due to mini sheet limitations:


Test.xlsx
AB
1DayHour of Day
21/1/202112:00 AM
31/1/20211:00 AM
41/1/20212:00 AM
51/1/20213:00 AM
61/1/20214:00 AM
71/1/20215:00 AM
81/1/20216:00 AM
91/1/20217:00 AM
101/1/20218:00 AM
111/1/20219:00 AM
121/1/202110:00 AM
131/1/202111:00 AM
141/1/202112:00 PM
151/1/20211:00 PM
161/1/20212:00 PM
171/1/20213:00 PM
181/1/20214:00 PM
191/1/20215:00 PM
201/1/20216:00 PM
211/1/20217:00 PM
221/1/20218:00 PM
231/1/20219:00 PM
241/1/202110:00 PM
251/1/202111:00 PM
261/2/202112:00 AM
271/2/20211:00 AM
281/2/20212:00 AM
291/2/20213:00 AM
301/2/20214:00 AM
311/2/20215:00 AM
321/2/20216:00 AM
331/2/20217:00 AM
341/2/20218:00 AM
351/2/20219:00 AM
361/2/202110:00 AM
371/2/202111:00 AM
381/2/202112:00 PM
391/2/20211:00 PM
401/2/20212:00 PM
411/2/20213:00 PM
421/2/20214:00 PM
431/2/20215:00 PM
441/2/20216:00 PM
451/2/20217:00 PM
461/2/20218:00 PM
471/2/20219:00 PM
481/2/202110:00 PM
491/2/202111:00 PM
501/3/202112:00 AM
511/3/20211:00 AM
521/3/20212:00 AM
531/3/20213:00 AM
541/3/20214:00 AM
551/3/20215:00 AM
561/3/20216:00 AM
571/3/20217:00 AM
581/3/20218:00 AM
591/3/20219:00 AM
601/3/202110:00 AM
611/3/202111:00 AM
621/3/202112:00 PM
631/3/20211:00 PM
641/3/20212:00 PM
651/3/20213:00 PM
661/3/20214:00 PM
671/3/20215:00 PM
681/3/20216:00 PM
691/3/20217:00 PM
701/3/20218:00 PM
711/3/20219:00 PM
721/3/202110:00 PM
731/3/202111:00 PM
741/4/202112:00 AM
751/4/20211:00 AM
761/4/20212:00 AM
771/4/20213:00 AM
781/4/20214:00 AM
791/4/20215:00 AM
801/4/20216:00 AM
811/4/20217:00 AM
821/4/20218:00 AM
831/4/20219:00 AM
841/4/202110:00 AM
851/4/202111:00 AM
861/4/202112:00 PM
871/4/20211:00 PM
881/4/20212:00 PM
891/4/20213:00 PM
901/4/20214:00 PM
911/4/20215:00 PM
921/4/20216:00 PM
931/4/20217:00 PM
941/4/20218:00 PM
951/4/20219:00 PM
961/4/202110:00 PM
971/4/202111:00 PM
981/5/202112:00 AM
991/5/20211:00 AM
1001/5/20212:00 AM
1011/5/20213:00 AM
1021/5/20214:00 AM
1031/5/20215:00 AM
1041/5/20216:00 AM
1051/5/20217:00 AM
1061/5/20218:00 AM
1071/5/20219:00 AM
1081/5/202110:00 AM
1091/5/202111:00 AM
1101/5/202112:00 PM
1111/5/20211:00 PM
1121/5/20212:00 PM
1131/5/20213:00 PM
1141/5/20214:00 PM
1151/5/20215:00 PM
1161/5/20216:00 PM
1171/5/20217:00 PM
1181/5/20218:00 PM
1191/5/20219:00 PM
1201/5/202110:00 PM
1211/5/202111:00 PM
1221/6/202112:00 AM
1231/6/20211:00 AM
1241/6/20212:00 AM
1251/6/20213:00 AM
1261/6/20214:00 AM
1271/6/20215:00 AM
1281/6/20216:00 AM
1291/6/20217:00 AM
1301/6/20218:00 AM
1311/6/20219:00 AM
1321/6/202110:00 AM
1331/6/202111:00 AM
1341/6/202112:00 PM
1351/6/20211:00 PM
1361/6/20212:00 PM
1371/6/20213:00 PM
1381/6/20214:00 PM
1391/6/20215:00 PM
1401/6/20216:00 PM
1411/6/20217:00 PM
1421/6/20218:00 PM
1431/6/20219:00 PM
1441/6/202110:00 PM
1451/6/202111:00 PM
1461/7/202112:00 AM
1471/7/20211:00 AM
1481/7/20212:00 AM
1491/7/20213:00 AM
1501/7/20214:00 AM
1511/7/20215:00 AM
1521/7/20216:00 AM
1531/7/20217:00 AM
1541/7/20218:00 AM
1551/7/20219:00 AM
1561/7/202110:00 AM
1571/7/202111:00 AM
1581/7/202112:00 PM
1591/7/20211:00 PM
1601/7/20212:00 PM
1611/7/20213:00 PM
1621/7/20214:00 PM
1631/7/20215:00 PM
1641/7/20216:00 PM
1651/7/20217:00 PM
1661/7/20218:00 PM
1671/7/20219:00 PM
1681/7/202110:00 PM
1691/7/202111:00 PM
1701/8/202112:00 AM
1711/8/20211:00 AM
1721/8/20212:00 AM
1731/8/20213:00 AM
1741/8/20214:00 AM
1751/8/20215:00 AM
1761/8/20216:00 AM
1771/8/20217:00 AM
1781/8/20218:00 AM
1791/8/20219:00 AM
1801/8/202110:00 AM
1811/8/202111:00 AM
1821/8/202112:00 PM
1831/8/20211:00 PM
1841/8/20212:00 PM
1851/8/20213:00 PM
1861/8/20214:00 PM
1871/8/20215:00 PM
1881/8/20216:00 PM
1891/8/20217:00 PM
1901/8/20218:00 PM
1911/8/20219:00 PM
1921/8/202110:00 PM
1931/8/202111:00 PM
1941/9/202112:00 AM
1951/9/20211:00 AM
1961/9/20212:00 AM
1971/9/20213:00 AM
1981/9/20214:00 AM
1991/9/20215:00 AM
2001/9/20216:00 AM
2011/9/20217:00 AM
2021/9/20218:00 AM
2031/9/20219:00 AM
2041/9/202110:00 AM
2051/9/202111:00 AM
2061/9/202112:00 PM
2071/9/20211:00 PM
2081/9/20212:00 PM
2091/9/20213:00 PM
2101/9/20214:00 PM
2111/9/20215:00 PM
2121/9/20216:00 PM
2131/9/20217:00 PM
2141/9/20218:00 PM
2151/9/20219:00 PM
2161/9/202110:00 PM
2171/9/202111:00 PM
2181/10/202112:00 AM
2191/10/20211:00 AM
2201/10/20212:00 AM
2211/10/20213:00 AM
2221/10/20214:00 AM
2231/10/20215:00 AM
2241/10/20216:00 AM
2251/10/20217:00 AM
2261/10/20218:00 AM
2271/10/20219:00 AM
2281/10/202110:00 AM
2291/10/202111:00 AM
2301/10/202112:00 PM
2311/10/20211:00 PM
2321/10/20212:00 PM
2331/10/20213:00 PM
2341/10/20214:00 PM
2351/10/20215:00 PM
2361/10/20216:00 PM
2371/10/20217:00 PM
2381/10/20218:00 PM
2391/10/20219:00 PM
2401/10/202110:00 PM
2411/10/202111:00 PM
2421/11/202112:00 AM
2431/11/20211:00 AM
2441/11/20212:00 AM
2451/11/20213:00 AM
2461/11/20214:00 AM
2471/11/20215:00 AM
2481/11/20216:00 AM
2491/11/20217:00 AM
2501/11/20218:00 AM
2511/11/20219:00 AM
2521/11/202110:00 AM
2531/11/202111:00 AM
2541/11/202112:00 PM
2551/11/20211:00 PM
2561/11/20212:00 PM
2571/11/20213:00 PM
2581/11/20214:00 PM
2591/11/20215:00 PM
2601/11/20216:00 PM
2611/11/20217:00 PM
2621/11/20218:00 PM
2631/11/20219:00 PM
2641/11/202110:00 PM
2651/11/202111:00 PM
2661/12/202112:00 AM
2671/12/20211:00 AM
2681/12/20212:00 AM
2691/12/20213:00 AM
2701/12/20214:00 AM
2711/12/20215:00 AM
2721/12/20216:00 AM
2731/12/20217:00 AM
2741/12/20218:00 AM
2751/12/20219:00 AM
2761/12/202110:00 AM
2771/12/202111:00 AM
2781/12/202112:00 PM
2791/12/20211:00 PM
2801/12/20212:00 PM
2811/12/20213:00 PM
2821/12/20214:00 PM
2831/12/20215:00 PM
2841/12/20216:00 PM
2851/12/20217:00 PM
2861/12/20218:00 PM
2871/12/20219:00 PM
2881/12/202110:00 PM
2891/12/202111:00 PM
2901/13/202112:00 AM
2911/13/20211:00 AM
2921/13/20212:00 AM
2931/13/20213:00 AM
2941/13/20214:00 AM
2951/13/20215:00 AM
2961/13/20216:00 AM
2971/13/20217:00 AM
2981/13/20218:00 AM
2991/13/20219:00 AM
3001/13/202110:00 AM
3011/13/202111:00 AM
3021/13/202112:00 PM
3031/13/20211:00 PM
3041/13/20212:00 PM
3051/13/20213:00 PM
3061/13/20214:00 PM
3071/13/20215:00 PM
3081/13/20216:00 PM
3091/13/20217:00 PM
3101/13/20218:00 PM
3111/13/20219:00 PM
3121/13/202110:00 PM
3131/13/202111:00 PM
3141/14/202112:00 AM
3151/14/20211:00 AM
3161/14/20212:00 AM
3171/14/20213:00 AM
3181/14/20214:00 AM
3191/14/20215:00 AM
3201/14/20216:00 AM
3211/14/20217:00 AM
3221/14/20218:00 AM
3231/14/20219:00 AM
3241/14/202110:00 AM
3251/14/202111:00 AM
3261/14/202112:00 PM
3271/14/20211:00 PM
3281/14/20212:00 PM
3291/14/20213:00 PM
3301/14/20214:00 PM
3311/14/20215:00 PM
3321/14/20216:00 PM
3331/14/20217:00 PM
3341/14/20218:00 PM
3351/14/20219:00 PM
3361/14/202110:00 PM
3371/14/202111:00 PM
3381/15/202112:00 AM
3391/15/20211:00 AM
3401/15/20212:00 AM
3411/15/20213:00 AM
3421/15/20214:00 AM
3431/15/20215:00 AM
3441/15/20216:00 AM
3451/15/20217:00 AM
3461/15/20218:00 AM
3471/15/20219:00 AM
3481/15/202110:00 AM
3491/15/202111:00 AM
3501/15/202112:00 PM
3511/15/20211:00 PM
3521/15/20212:00 PM
3531/15/20213:00 PM
3541/15/20214:00 PM
3551/15/20215:00 PM
3561/15/20216:00 PM
3571/15/20217:00 PM
3581/15/20218:00 PM
3591/15/20219:00 PM
3601/15/202110:00 PM
3611/15/202111:00 PM
3621/16/202112:00 AM
3631/16/20211:00 AM
3641/16/20212:00 AM
3651/16/20213:00 AM
3661/16/20214:00 AM
3671/16/20215:00 AM
3681/16/20216:00 AM
3691/16/20217:00 AM
3701/16/20218:00 AM
3711/16/20219:00 AM
3721/16/202110:00 AM
3731/16/202111:00 AM
3741/16/202112:00 PM
3751/16/20211:00 PM
3761/16/20212:00 PM
3771/16/20213:00 PM
3781/16/20214:00 PM
3791/16/20215:00 PM
3801/16/20216:00 PM
3811/16/20217:00 PM
3821/16/20218:00 PM
3831/16/20219:00 PM
3841/16/202110:00 PM
3851/16/202111:00 PM
3861/17/202112:00 AM
3871/17/20211:00 AM
3881/17/20212:00 AM
3891/17/20213:00 AM
3901/17/20214:00 AM
3911/17/20215:00 AM
3921/17/20216:00 AM
3931/17/20217:00 AM
3941/17/20218:00 AM
3951/17/20219:00 AM
3961/17/202110:00 AM
3971/17/202111:00 AM
3981/17/202112:00 PM
3991/17/20211:00 PM
4001/17/20212:00 PM
4011/17/20213:00 PM
4021/17/20214:00 PM
4031/17/20215:00 PM
4041/17/20216:00 PM
4051/17/20217:00 PM
4061/17/20218:00 PM
4071/17/20219:00 PM
4081/17/202110:00 PM
4091/17/202111:00 PM
4101/18/202112:00 AM
4111/18/20211:00 AM
4121/18/20212:00 AM
4131/18/20213:00 AM
4141/18/20214:00 AM
4151/18/20215:00 AM
4161/18/20216:00 AM
4171/18/20217:00 AM
4181/18/20218:00 AM
4191/18/20219:00 AM
4201/18/202110:00 AM
4211/18/202111:00 AM
4221/18/202112:00 PM
4231/18/20211:00 PM
4241/18/20212:00 PM
4251/18/20213:00 PM
4261/18/20214:00 PM
4271/18/20215:00 PM
4281/18/20216:00 PM
4291/18/20217:00 PM
4301/18/20218:00 PM
4311/18/20219:00 PM
4321/18/202110:00 PM
4331/18/202111:00 PM
4341/19/202112:00 AM
4351/19/20211:00 AM
4361/19/20212:00 AM
4371/19/20213:00 AM
4381/19/20214:00 AM
4391/19/20215:00 AM
4401/19/20216:00 AM
4411/19/20217:00 AM
4421/19/20218:00 AM
4431/19/20219:00 AM
4441/19/202110:00 AM
4451/19/202111:00 AM
4461/19/202112:00 PM
4471/19/20211:00 PM
4481/19/20212:00 PM
4491/19/20213:00 PM
4501/19/20214:00 PM
4511/19/20215:00 PM
4521/19/20216:00 PM
4531/19/20217:00 PM
4541/19/20218:00 PM
4551/19/20219:00 PM
4561/19/202110:00 PM
4571/19/202111:00 PM
4581/20/202112:00 AM
4591/20/20211:00 AM
4601/20/20212:00 AM
4611/20/20213:00 AM
4621/20/20214:00 AM
4631/20/20215:00 AM
4641/20/20216:00 AM
4651/20/20217:00 AM
4661/20/20218:00 AM
4671/20/20219:00 AM
4681/20/202110:00 AM
4691/20/202111:00 AM
4701/20/202112:00 PM
4711/20/20211:00 PM
4721/20/20212:00 PM
4731/20/20213:00 PM
4741/20/20214:00 PM
4751/20/20215:00 PM
4761/20/20216:00 PM
4771/20/20217:00 PM
4781/20/20218:00 PM
4791/20/20219:00 PM
4801/20/202110:00 PM
4811/20/202111:00 PM
4821/21/202112:00 AM
4831/21/20211:00 AM
4841/21/20212:00 AM
4851/21/20213:00 AM
4861/21/20214:00 AM
4871/21/20215:00 AM
4881/21/20216:00 AM
4891/21/20217:00 AM
4901/21/20218:00 AM
4911/21/20219:00 AM
4921/21/202110:00 AM
4931/21/202111:00 AM
4941/21/202112:00 PM
4951/21/20211:00 PM
4961/21/20212:00 PM
4971/21/20213:00 PM
4981/21/20214:00 PM
4991/21/20215:00 PM
5001/21/20216:00 PM
5011/21/20217:00 PM
5021/21/20218:00 PM
5031/21/20219:00 PM
5041/21/202110:00 PM
5051/21/202111:00 PM
5061/22/202112:00 AM
5071/22/20211:00 AM
5081/22/20212:00 AM
5091/22/20213:00 AM
5101/22/20214:00 AM
5111/22/20215:00 AM
5121/22/20216:00 AM
5131/22/20217:00 AM
5141/22/20218:00 AM
5151/22/20219:00 AM
5161/22/202110:00 AM
5171/22/202111:00 AM
5181/22/202112:00 PM
5191/22/20211:00 PM
5201/22/20212:00 PM
5211/22/20213:00 PM
5221/22/20214:00 PM
5231/22/20215:00 PM
5241/22/20216:00 PM
5251/22/20217:00 PM
5261/22/20218:00 PM
5271/22/20219:00 PM
5281/22/202110:00 PM
5291/22/202111:00 PM
5301/23/202112:00 AM
5311/23/20211:00 AM
5321/23/20212:00 AM
5331/23/20213:00 AM
5341/23/20214:00 AM
5351/23/20215:00 AM
5361/23/20216:00 AM
5371/23/20217:00 AM
5381/23/20218:00 AM
5391/23/20219:00 AM
5401/23/202110:00 AM
5411/23/202111:00 AM
5421/23/202112:00 PM
5431/23/20211:00 PM
5441/23/20212:00 PM
5451/23/20213:00 PM
5461/23/20214:00 PM
5471/23/20215:00 PM
5481/23/20216:00 PM
5491/23/20217:00 PM
5501/23/20218:00 PM
5511/23/20219:00 PM
5521/23/202110:00 PM
5531/23/202111:00 PM
5541/24/202112:00 AM
5551/24/20211:00 AM
5561/24/20212:00 AM
5571/24/20213:00 AM
5581/24/20214:00 AM
5591/24/20215:00 AM
5601/24/20216:00 AM
5611/24/20217:00 AM
5621/24/20218:00 AM
5631/24/20219:00 AM
5641/24/202110:00 AM
5651/24/202111:00 AM
5661/24/202112:00 PM
5671/24/20211:00 PM
5681/24/20212:00 PM
5691/24/20213:00 PM
5701/24/20214:00 PM
5711/24/20215:00 PM
5721/24/20216:00 PM
5731/24/20217:00 PM
5741/24/20218:00 PM
5751/24/20219:00 PM
5761/24/202110:00 PM
5771/24/202111:00 PM
5781/25/202112:00 AM
5791/25/20211:00 AM
5801/25/20212:00 AM
5811/25/20213:00 AM
5821/25/20214:00 AM
5831/25/20215:00 AM
5841/25/20216:00 AM
5851/25/20217:00 AM
5861/25/20218:00 AM
5871/25/20219:00 AM
5881/25/202110:00 AM
5891/25/202111:00 AM
5901/25/202112:00 PM
5911/25/20211:00 PM
5921/25/20212:00 PM
5931/25/20213:00 PM
5941/25/20214:00 PM
5951/25/20215:00 PM
5961/25/20216:00 PM
5971/25/20217:00 PM
5981/25/20218:00 PM
5991/25/20219:00 PM
6001/25/202110:00 PM
6011/25/202111:00 PM
6021/26/202112:00 AM
6031/26/20211:00 AM
6041/26/20212:00 AM
6051/26/20213:00 AM
6061/26/20214:00 AM
6071/26/20215:00 AM
6081/26/20216:00 AM
6091/26/20217:00 AM
6101/26/20218:00 AM
6111/26/20219:00 AM
6121/26/202110:00 AM
6131/26/202111:00 AM
6141/26/202112:00 PM
6151/26/20211:00 PM
6161/26/20212:00 PM
6171/26/20213:00 PM
6181/26/20214:00 PM
6191/26/20215:00 PM
6201/26/20216:00 PM
6211/26/20217:00 PM
6221/26/20218:00 PM
6231/26/20219:00 PM
6241/26/202110:00 PM
6251/26/202111:00 PM
6261/27/202112:00 AM
6271/27/20211:00 AM
6281/27/20212:00 AM
6291/27/20213:00 AM
6301/27/20214:00 AM
6311/27/20215:00 AM
6321/27/20216:00 AM
6331/27/20217:00 AM
6341/27/20218:00 AM
6351/27/20219:00 AM
6361/27/202110:00 AM
6371/27/202111:00 AM
6381/27/202112:00 PM
6391/27/20211:00 PM
6401/27/20212:00 PM
6411/27/20213:00 PM
6421/27/20214:00 PM
6431/27/20215:00 PM
6441/27/20216:00 PM
6451/27/20217:00 PM
6461/27/20218:00 PM
6471/27/20219:00 PM
6481/27/202110:00 PM
6491/27/202111:00 PM
6501/28/202112:00 AM
6511/28/20211:00 AM
6521/28/20212:00 AM
6531/28/20213:00 AM
6541/28/20214:00 AM
6551/28/20215:00 AM
6561/28/20216:00 AM
6571/28/20217:00 AM
6581/28/20218:00 AM
6591/28/20219:00 AM
6601/28/202110:00 AM
6611/28/202111:00 AM
6621/28/202112:00 PM
6631/28/20211:00 PM
6641/28/20212:00 PM
6651/28/20213:00 PM
6661/28/20214:00 PM
6671/28/20215:00 PM
6681/28/20216:00 PM
6691/28/20217:00 PM
6701/28/20218:00 PM
6711/28/20219:00 PM
6721/28/202110:00 PM
6731/28/202111:00 PM
6741/29/202112:00 AM
6751/29/20211:00 AM
6761/29/20212:00 AM
6771/29/20213:00 AM
6781/29/20214:00 AM
6791/29/20215:00 AM
6801/29/20216:00 AM
6811/29/20217:00 AM
6821/29/20218:00 AM
6831/29/20219:00 AM
6841/29/202110:00 AM
6851/29/202111:00 AM
6861/29/202112:00 PM
6871/29/20211:00 PM
6881/29/20212:00 PM
6891/29/20213:00 PM
6901/29/20214:00 PM
6911/29/20215:00 PM
6921/29/20216:00 PM
6931/29/20217:00 PM
6941/29/20218:00 PM
6951/29/20219:00 PM
6961/29/202110:00 PM
6971/29/202111:00 PM
6981/30/202112:00 AM
6991/30/20211:00 AM
7001/30/20212:00 AM
7011/30/20213:00 AM
7021/30/20214:00 AM
7031/30/20215:00 AM
7041/30/20216:00 AM
7051/30/20217:00 AM
7061/30/20218:00 AM
7071/30/20219:00 AM
7081/30/202110:00 AM
7091/30/202111:00 AM
7101/30/202112:00 PM
7111/30/20211:00 PM
7121/30/20212:00 PM
7131/30/20213:00 PM
7141/30/20214:00 PM
7151/30/20215:00 PM
7161/30/20216:00 PM
7171/30/20217:00 PM
7181/30/20218:00 PM
7191/30/20219:00 PM
7201/30/202110:00 PM
7211/30/202111:00 PM
7221/31/202112:00 AM
7231/31/20211:00 AM
7241/31/20212:00 AM
7251/31/20213:00 AM
7261/31/20214:00 AM
7271/31/20215:00 AM
7281/31/20216:00 AM
7291/31/20217:00 AM
7301/31/20218:00 AM
7311/31/20219:00 AM
7321/31/202110:00 AM
7331/31/202111:00 AM
7341/31/202112:00 PM
7351/31/20211:00 PM
7361/31/20212:00 PM
7371/31/20213:00 PM
7381/31/20214:00 PM
7391/31/20215:00 PM
7401/31/20216:00 PM
7411/31/20217:00 PM
7421/31/20218:00 PM
7431/31/20219:00 PM
7441/31/202110:00 PM
7451/31/202111:00 PM
7462/1/202112:00 AM
7472/1/20211:00 AM
7482/1/20212:00 AM
7492/1/20213:00 AM
7502/1/20214:00 AM
7512/1/20215:00 AM
7522/1/20216:00 AM
7532/1/20217:00 AM
7542/1/20218:00 AM
7552/1/20219:00 AM
7562/1/202110:00 AM
7572/1/202111:00 AM
7582/1/202112:00 PM
7592/1/20211:00 PM
7602/1/20212:00 PM
7612/1/20213:00 PM
7622/1/20214:00 PM
7632/1/20215:00 PM
7642/1/20216:00 PM
7652/1/20217:00 PM
7662/1/20218:00 PM
7672/1/20219:00 PM
7682/1/202110:00 PM
7692/1/202111:00 PM
7702/2/202112:00 AM
7712/2/20211:00 AM
7722/2/20212:00 AM
7732/2/20213:00 AM
7742/2/20214:00 AM
7752/2/20215:00 AM
7762/2/20216:00 AM
7772/2/20217:00 AM
7782/2/20218:00 AM
7792/2/20219:00 AM
7802/2/202110:00 AM
7812/2/202111:00 AM
7822/2/202112:00 PM
7832/2/20211:00 PM
7842/2/20212:00 PM
7852/2/20213:00 PM
7862/2/20214:00 PM
7872/2/20215:00 PM
7882/2/20216:00 PM
7892/2/20217:00 PM
7902/2/20218:00 PM
7912/2/20219:00 PM
7922/2/202110:00 PM
7932/2/202111:00 PM
7942/3/202112:00 AM
7952/3/20211:00 AM
7962/3/20212:00 AM
7972/3/20213:00 AM
7982/3/20214:00 AM
7992/3/20215:00 AM
8002/3/20216:00 AM
8012/3/20217:00 AM
8022/3/20218:00 AM
8032/3/20219:00 AM
8042/3/202110:00 AM
8052/3/202111:00 AM
8062/3/202112:00 PM
8072/3/20211:00 PM
8082/3/20212:00 PM
8092/3/20213:00 PM
8102/3/20214:00 PM
8112/3/20215:00 PM
8122/3/20216:00 PM
8132/3/20217:00 PM
8142/3/20218:00 PM
8152/3/20219:00 PM
8162/3/202110:00 PM
8172/3/202111:00 PM
8182/4/202112:00 AM
8192/4/20211:00 AM
8202/4/20212:00 AM
8212/4/20213:00 AM
8222/4/20214:00 AM
8232/4/20215:00 AM
8242/4/20216:00 AM
8252/4/20217:00 AM
8262/4/20218:00 AM
8272/4/20219:00 AM
8282/4/202110:00 AM
8292/4/202111:00 AM
8302/4/202112:00 PM
8312/4/20211:00 PM
8322/4/20212:00 PM
8332/4/20213:00 PM
8342/4/20214:00 PM
8352/4/20215:00 PM
8362/4/20216:00 PM
8372/4/20217:00 PM
8382/4/20218:00 PM
8392/4/20219:00 PM
8402/4/202110:00 PM
8412/4/202111:00 PM
8422/5/202112:00 AM
8432/5/20211:00 AM
8442/5/20212:00 AM
8452/5/20213:00 AM
8462/5/20214:00 AM
8472/5/20215:00 AM
8482/5/20216:00 AM
8492/5/20217:00 AM
8502/5/20218:00 AM
8512/5/20219:00 AM
8522/5/202110:00 AM
8532/5/202111:00 AM
8542/5/202112:00 PM
8552/5/20211:00 PM
8562/5/20212:00 PM
8572/5/20213:00 PM
8582/5/20214:00 PM
8592/5/20215:00 PM
8602/5/20216:00 PM
8612/5/20217:00 PM
8622/5/20218:00 PM
8632/5/20219:00 PM
8642/5/202110:00 PM
8652/5/202111:00 PM
Usage
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
See if this does what you want.

Excel Formula:
=TEXT(EDATE(LOOKUP(9^9,A:A),(LOOKUP(9^9,A:A)=EOMONTH(LOOKUP(9^9,A:A),0))-1),"mmm yyyy")
 

PrachiOU

New Member
Joined
Apr 1, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

See if this does what you want.

Excel Formula:
=TEXT(EDATE(LOOKUP(9^9,A:A),(LOOKUP(9^9,A:A)=EOMONTH(LOOKUP(9^9,A:A),0))-1),"mmm yyyy")
Thankyou for the prompt help Peter_SSs
I updated my range in the above code u gave and its giving me Dec 2019 and Dec 2019 is not in my Date range.
 

PrachiOU

New Member
Joined
Apr 1, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Thankyou for the prompt help Peter_SSs
I updated my range in the above code u gave and its giving me Dec 2019 and Dec 2019 is not in my Date range.
Peter_SSs, thanks a ton for the code i updated it to following and its working:

=TEXT(EDATE(MAX(Dates),(MAX(Dates)=EOMONTH(MAX(Dates),0))-1),"mmm yyyy")
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
Glad you got it to work. Thanks for letting us know. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,137
Messages
5,640,329
Members
417,139
Latest member
madcabbie

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
Top