count number of unique dates

bo_danseuer

New Member
Joined
Oct 23, 2012
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
Hey Excel Junkies,

I have been trying to find a "sumproduct" solution (or any solution really) for a simple problem. I saw quite a number of postes on here with similar problems but not exactly my problem. Everyone wants to count something or other based on various criteria. I just want to count the number of unique dates for a certain range but cannot make it work.

I can successfully use sumproduct to calculate how many entries for example in Jan 2021 or to calculate how many VVI entries in 2020. Can anyone help me count the number of days an entry was made in a certain time frame ?

NB: This is part of a reporting process with various pivot tables and charts all connected to a slicer where the user can select his reporting month.
So there is a cell for "Report Month" = "Jan 2021"
I use the following formula to count (for example) how many "VVI" entries in the Report Month (RptMth) answer 2 for Mar 2020 based on the beginning date :

=SUMPRODUCT((INT(begin)>=(VALUE(rptMth)))*(INT(begin)<=EOMONTH(rptMth;0))*(type="VVI"))

Or to figure out the pay amount earned (the "pay" column is missing in the data) :

=SUMPRODUCT((INT(begin)>=(VALUE(rptMth)))*(INT(begin)<=EOMONTH(rptMth;0))*pay)

Can someone help me figure out how to count the unique number of days an entry was made?
For Jan 2021 the result should be 3 (4th, 5th and 6th Jan) ;
for March 2020 it's 2 (1st and 2nd Mar) .

Many thank (and a pint to the genius) who figures this out.
Cheers,
RvL


sumproduct play data.xlsx
CDE
1BeginEndType
202 Mar 20 (00:00:00)01 Mar 21 (00:00:00)VVI
301 Mar 20 (09:20:00)04 Jan 21 (09:29:00)PAR
402 Mar 20 (00:00:00)01 Mar 21 (00:00:00)VVI
501 Mar 20 (09:20:00)04 Jan 21 (09:29:00)PAR
604 Jan 21 (11:11:00)04 Jan 21 (11:22:00)PAR
704 Jan 21 (11:28:00)04 Jan 21 (11:31:00)PAR
804 Jan 21 (12:38:00)04 Jan 21 (12:54:00)PAR
904 Jan 21 (17:04:00)04 Jan 21 (17:05:00)PAR
1004 Jan 21 (17:07:00)04 Jan 21 (17:09:00)PAR
1104 Jan 21 (17:12:00)04 Jan 21 (17:43:00)PAR
1204 Jan 21 (17:48:00)04 Jan 21 (18:26:00)PAR
1304 Jan 21 (18:27:00)04 Jan 21 (18:31:00)PAR
1404 Jan 21 (18:42:00)04 Jan 21 (19:15:00)PAR
1504 Jan 21 (19:33:00)04 Jan 21 (19:40:00)PAR
1604 Jan 21 (19:42:00)04 Jan 21 (19:49:00)PAR
1704 Jan 21 (19:50:00)04 Jan 21 (20:06:00)PAR
1804 Jan 21 (20:06:00)04 Jan 21 (20:10:00)PAR
1904 Jan 21 (20:10:00)04 Jan 21 (20:44:00)PAR
2004 Jan 21 (20:50:00)04 Jan 21 (20:56:00)PAR
2104 Jan 21 (21:00:00)04 Jan 21 (21:05:00)PAR
2205 Jan 21 (09:06:00)05 Jan 21 (09:17:00)PAR
2305 Jan 21 (11:53:00)05 Jan 21 (11:54:00)PAR
2405 Jan 21 (12:05:00)05 Jan 21 (12:57:00)PAR
2505 Jan 21 (16:28:00)05 Jan 21 (16:29:00)PAR
2605 Jan 21 (16:32:00)05 Jan 21 (17:13:00)PAR
2705 Jan 21 (17:40:00)05 Jan 21 (17:54:00)PAR
2805 Jan 21 (17:59:00)05 Jan 21 (18:51:00)PAR
2905 Jan 21 (19:04:00)05 Jan 21 (19:06:00)PAR
3005 Jan 21 (19:14:00)05 Jan 21 (19:15:00)PAR
3105 Jan 21 (19:22:00)05 Jan 21 (19:30:00)PAR
3205 Jan 21 (19:33:00)05 Jan 21 (19:37:00)PAR
3305 Jan 21 (19:41:00)05 Jan 21 (21:34:00)PAR
3406 Jan 21 (09:47:00)06 Jan 21 (09:55:00)PAR
3506 Jan 21 (10:55:00)06 Jan 21 (10:59:00)PAR
3606 Jan 21 (11:17:00)06 Jan 21 (11:43:00)PAR
3706 Jan 21 (15:40:00)06 Jan 21 (15:55:00)PAR
3806 Jan 21 (16:06:00)06 Jan 21 (16:40:00)PAR
3906 Jan 21 (16:41:00)06 Jan 21 (16:54:00)PAR
4006 Jan 21 (16:56:00)06 Jan 21 (17:10:00)PAR
Sheet1
 

Attachments

  • 1618415973585.png
    1618415973585.png
    33.7 KB · Views: 2

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
CDEFG
1BeginEndType01/01/2021
202/03/202001/03/2021VVI3
301/03/202004/01/2021PAR
402/03/202001/03/2021VVI
501/03/202004/01/2021PAR
604/01/202104/01/2021PAR
704/01/202104/01/2021PAR
804/01/202104/01/2021PAR
904/01/202104/01/2021PAR
1004/01/202104/01/2021PAR
1104/01/202104/01/2021PAR
1204/01/202104/01/2021PAR
1304/01/202104/01/2021PAR
1404/01/202104/01/2021PAR
1504/01/202104/01/2021PAR
1604/01/202104/01/2021PAR
1704/01/202104/01/2021PAR
1804/01/202104/01/2021PAR
1904/01/202104/01/2021PAR
2004/01/202104/01/2021PAR
2104/01/202104/01/2021PAR
2205/01/202105/01/2021PAR
2305/01/202105/01/2021PAR
2405/01/202105/01/2021PAR
2505/01/202105/01/2021PAR
2605/01/202105/01/2021PAR
2705/01/202105/01/2021PAR
2805/01/202105/01/2021PAR
2905/01/202105/01/2021PAR
3005/01/202105/01/2021PAR
3105/01/202105/01/2021PAR
3205/01/202105/01/2021PAR
3305/01/202105/01/2021PAR
3406/01/202106/01/2021PAR
3506/01/202106/01/2021PAR
3606/01/202106/01/2021PAR
3706/01/202106/01/2021PAR
3806/01/202106/01/2021PAR
3906/01/202106/01/2021PAR
4006/01/202106/01/2021PAR
Control
Cell Formulas
RangeFormula
G2G2=ROWS(UNIQUE(FILTER(DAY(C2:C40),TEXT(C2:C40,"mm/yyyy")=TEXT(G1,"mm/yyyy"))))
 
Solution

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,768
Office Version
  1. 365
  2. 2010
Haven't figured out a way to combine this, but the helper columns seem to do it.

Book1
GHI
2Mar 20Mar 202
3Mar 20Jan 213
4Jan 21
5Jan 21
6Jan 21
Sheet1
Cell Formulas
RangeFormula
G2:G6G2=RIGHT(UNIQUE(LEFT(C2:C40&D2:D40,9)),6)
H2:H3H2=UNIQUE(RIGHT(G2#,6))
I2:I3I2=COUNTIF($G$2#,H2)
Dynamic array formulas.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,190
Office Version
  1. 365
Platform
  1. Windows
An alternative is Power Query.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Begin", type date}, {"End", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"End", "Type"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Begin"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
    #"Inserted Month Name" = Table.AddColumn(#"Grouped Rows", "Month Name", each Date.MonthName([Begin]), type text),
    #"Grouped Rows1" = Table.Group(#"Inserted Month Name", {"Month Name"}, {{"Sum", each List.Sum([Count]), type number}})
in
    #"Grouped Rows1"

Book1
AB
1Month NameSum
2March2
3January3
Table1


EDIT: Sorry, just looked and saw you are running a MAC. Don't think this functionality is available for you.
 

bo_danseuer

New Member
Joined
Oct 23, 2012
Messages
6
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

How about
+Fluff 1.xlsm
CDEFG
1BeginEndType01/01/2021
202/03/202001/03/2021VVI3
301/03/202004/01/2021PAR
402/03/202001/03/2021VVI
501/03/202004/01/2021PAR
604/01/202104/01/2021PAR
704/01/202104/01/2021PAR
804/01/202104/01/2021PAR
904/01/202104/01/2021PAR
1004/01/202104/01/2021PAR
1104/01/202104/01/2021PAR
1204/01/202104/01/2021PAR
1304/01/202104/01/2021PAR
1404/01/202104/01/2021PAR
1504/01/202104/01/2021PAR
1604/01/202104/01/2021PAR
1704/01/202104/01/2021PAR
1804/01/202104/01/2021PAR
1904/01/202104/01/2021PAR
2004/01/202104/01/2021PAR
2104/01/202104/01/2021PAR
2205/01/202105/01/2021PAR
2305/01/202105/01/2021PAR
2405/01/202105/01/2021PAR
2505/01/202105/01/2021PAR
2605/01/202105/01/2021PAR
2705/01/202105/01/2021PAR
2805/01/202105/01/2021PAR
2905/01/202105/01/2021PAR
3005/01/202105/01/2021PAR
3105/01/202105/01/2021PAR
3205/01/202105/01/2021PAR
3305/01/202105/01/2021PAR
3406/01/202106/01/2021PAR
3506/01/202106/01/2021PAR
3606/01/202106/01/2021PAR
3706/01/202106/01/2021PAR
3806/01/202106/01/2021PAR
3906/01/202106/01/2021PAR
4006/01/202106/01/2021PAR
Control
Cell Formulas
RangeFormula
G2G2=ROWS(UNIQUE(FILTER(DAY(C2:C40),TEXT(C2:C40,"mm/yyyy")=TEXT(G1,"mm/yyyy"))))

Cheers Fluff,
Cheers as well to the others who gave it a stab, but Fluff wins the pint (if ever the pubs open again)!

Fluff, you are a star !!!
I know of "rows" but never really come to grips with it. I've tried working with "unique" but with no great success and "filter" is completely new to me. I was looking for a simple formula and you delivered it. I've applied to my full data base and it works like a charm. Thank you.

Now I am going to try and figure out now how to apply it to the days of the week to see (for example) "how many Mondays" were worked.
I've been able to calculate the volume on "Mondays" (for exemple) but can't seem to count "how many Mondays were worked in a given Report Month".

I have five years of data with approximately 20 to 50 entries per day. My objective is to be able to see in any given "report month" whether there are certain days of the week that have a consistent (or periodical) higher volume. I can use this formula to calculate number of entries on Mondays
=SUMPRODUCT((INT(begin)>=(VALUE(rptMth)))*(INT(begin)<=EOMONTH(rptMth;0))*(WEEKDAY(begin;2)=1))

But what I can't figure out is "in any given month, how many Mondays, Tuesdays, Weds... etc were worked". If I know that, I can use it to calculate the average earned on those days and use this figure for staffing purposes.

Here is more elaborate data to play with.

sumproduct play data.xlsx
CDEJKL
1BeginEndTypeStd PayBegin ValueBegin Weekday
201 Mar 20 (09:20:00)04 Jan 21 (09:29:00)PAR$  4.4843891.38889Sunday
301 Mar 20 (09:20:00)04 Jan 21 (09:29:00)PAR$  4.4843891.38889Sunday
402 Mar 20 (00:00:00)01 Mar 21 (00:00:00)VVI$  20.0043892Monday
502 Mar 20 (00:00:00)01 Mar 21 (00:00:00)VVI$  20.0043892Monday
604 Jan 21 (11:11:00)04 Jan 21 (11:22:00)PAR$  6.1644200.46597Monday
704 Jan 21 (11:28:00)04 Jan 21 (11:31:00)PAR$  1.1244200.47778Monday
804 Jan 21 (12:38:00)04 Jan 21 (12:54:00)PAR$  7.8044200.52639Monday
904 Jan 21 (17:04:00)04 Jan 21 (17:05:00)PAR$  0.5244200.71111Monday
1004 Jan 21 (17:07:00)04 Jan 21 (17:09:00)PAR$  1.0444200.71319Monday
1104 Jan 21 (17:12:00)04 Jan 21 (17:43:00)PAR$  15.6044200.71667Monday
1204 Jan 21 (17:48:00)04 Jan 21 (18:26:00)PAR$  19.2444200.74167Monday
1304 Jan 21 (18:27:00)04 Jan 21 (18:31:00)PAR$  1.5644200.76875Monday
1404 Jan 21 (18:42:00)04 Jan 21 (19:15:00)PAR$  17.1644200.77917Monday
1504 Jan 21 (19:33:00)04 Jan 21 (19:40:00)PAR$  3.6444200.81458Monday
1604 Jan 21 (19:42:00)04 Jan 21 (19:49:00)PAR$  3.6444200.82083Monday
1704 Jan 21 (19:50:00)04 Jan 21 (20:06:00)PAR$  7.8044200.82639Monday
1804 Jan 21 (20:06:00)04 Jan 21 (20:10:00)PAR$  2.0844200.8375Monday
1904 Jan 21 (20:10:00)04 Jan 21 (20:44:00)PAR$  17.1644200.84028Monday
2004 Jan 21 (20:50:00)04 Jan 21 (20:56:00)PAR$  2.6044200.86806Monday
2104 Jan 21 (21:00:00)04 Jan 21 (21:05:00)PAR$  2.0844200.875Monday
2205 Jan 21 (09:06:00)05 Jan 21 (09:17:00)PAR$  6.1644201.37917Tuesday
2305 Jan 21 (11:53:00)05 Jan 21 (11:54:00)PAR$  0.5644201.49514Tuesday
2405 Jan 21 (12:05:00)05 Jan 21 (12:57:00)PAR$  27.0444201.50347Tuesday
2505 Jan 21 (16:32:00)05 Jan 21 (17:13:00)PAR$  21.3244201.68889Tuesday
2605 Jan 21 (17:40:00)05 Jan 21 (17:54:00)PAR$  7.2844201.73611Tuesday
2705 Jan 21 (17:59:00)05 Jan 21 (18:51:00)PAR$  27.0444201.74931Tuesday
2805 Jan 21 (19:04:00)05 Jan 21 (19:06:00)PAR$  0.5244201.79444Tuesday
2905 Jan 21 (19:14:00)05 Jan 21 (19:15:00)PAR$  0.5244201.80139Tuesday
3005 Jan 21 (19:22:00)05 Jan 21 (19:30:00)PAR$  4.1644201.80694Tuesday
3105 Jan 21 (19:33:00)05 Jan 21 (19:37:00)PAR$  2.0844201.81458Tuesday
3205 Jan 21 (19:41:00)05 Jan 21 (21:34:00)PAR$  58.2444201.82014Tuesday
3306 Jan 21 (09:47:00)06 Jan 21 (09:55:00)PAR$  4.4844202.40764Wednesday
3406 Jan 21 (10:55:00)06 Jan 21 (10:59:00)PAR$  2.2444202.45486Wednesday
3506 Jan 21 (11:17:00)06 Jan 21 (11:43:00)PAR$  14.5644202.47014Wednesday
3606 Jan 21 (15:40:00)06 Jan 21 (15:55:00)PAR$  7.2844202.65278Wednesday
3706 Jan 21 (16:06:00)06 Jan 21 (16:40:00)PAR$  17.6844202.67083Wednesday
3806 Jan 21 (16:41:00)06 Jan 21 (16:54:00)PAR$  6.7644202.69514Wednesday
3906 Jan 21 (16:56:00)06 Jan 21 (17:10:00)PAR$  7.2844202.70556Wednesday
4006 Jan 21 (17:11:00)06 Jan 21 (17:16:00)PAR$  2.6044202.71597Wednesday
4106 Jan 21 (17:17:00)06 Jan 21 (17:50:00)VVI$  17.1644202.72014Wednesday
4206 Jan 21 (18:04:00)06 Jan 21 (18:14:00)PAR$  5.2044202.75278Wednesday
4306 Jan 21 (18:21:00)06 Jan 21 (18:26:00)PAR$  2.6044202.76458Wednesday
4406 Jan 21 (18:32:00)06 Jan 21 (18:46:00)PAR$  7.2844202.77222Wednesday
4506 Jan 21 (18:51:00)06 Jan 21 (18:53:00)PAR$  1.0444202.78542Wednesday
4606 Jan 21 (18:54:00)06 Jan 21 (19:39:00)PAR$  23.4044202.7875Wednesday
4707 Jan 21 (09:40:00)07 Jan 21 (09:45:00)PAR$  2.2444203.40278Thursday
4807 Jan 21 (10:11:00)07 Jan 21 (10:27:00)PAR$  8.4044203.42431Thursday
4907 Jan 21 (16:49:00)07 Jan 21 (16:49:00)VVI$  0.0044203.70069Thursday
5007 Jan 21 (16:51:00)07 Jan 21 (16:54:00)PAR$  1.5644203.70208Thursday
5107 Jan 21 (16:54:00)07 Jan 21 (17:07:00)VVI$  6.2444203.70417Thursday
5207 Jan 21 (17:14:00)07 Jan 21 (18:10:00)PAR$  29.1244203.71806Thursday
5307 Jan 21 (18:13:00)07 Jan 21 (18:22:00)PAR$  4.1644203.75903Thursday
5407 Jan 21 (18:26:00)07 Jan 21 (18:38:00)PAR$  5.7244203.76806Thursday
5507 Jan 21 (18:48:00)07 Jan 21 (19:23:00)PAR$  17.6844203.78333Thursday
5607 Jan 21 (19:30:00)07 Jan 21 (19:34:00)PAR$  2.0844203.8125Thursday
5707 Jan 21 (19:40:00)07 Jan 21 (19:52:00)PAR$  5.7244203.81944Thursday
5807 Jan 21 (19:55:00)07 Jan 21 (20:31:00)PAR$  18.7244203.82986Thursday
5907 Jan 21 (20:32:00)07 Jan 21 (21:14:00)PAR$  21.8444203.85556Thursday
6008 Jan 21 (10:03:00)08 Jan 21 (10:15:00)PAR$  6.7244204.41875Friday
6108 Jan 21 (10:25:00)08 Jan 21 (10:38:00)PAR$  7.2844204.43403Friday
6208 Jan 21 (11:29:00)08 Jan 21 (11:34:00)PAR$  2.2444204.47847Friday
6308 Jan 21 (13:23:00)08 Jan 21 (13:25:00)PAR$  1.0444204.55764Friday
6408 Jan 21 (15:41:00)08 Jan 21 (15:58:00)PAR$  8.8444204.65347Friday
6508 Jan 21 (16:17:00)08 Jan 21 (16:18:00)PAR$  0.5244204.67847Friday
6608 Jan 21 (16:37:00)08 Jan 21 (16:41:00)PAR$  2.0844204.69236Friday
6708 Jan 21 (16:47:00)08 Jan 21 (16:55:00)PAR$  3.6444204.69931Friday
6808 Jan 21 (17:00:00)08 Jan 21 (17:14:00)PAR$  6.7644204.70833Friday
6908 Jan 21 (17:34:00)08 Jan 21 (17:38:00)PAR$  2.0844204.73194Friday
7008 Jan 21 (17:40:00)08 Jan 21 (17:47:00)PAR$  3.6444204.73611Friday
7108 Jan 21 (18:14:00)08 Jan 21 (18:36:00)PAR$  10.9244204.75972Friday
7208 Jan 21 (18:51:00)08 Jan 21 (19:13:00)PAR$  10.9244204.78542Friday
7308 Jan 21 (19:18:00)08 Jan 21 (19:30:00)PAR$  5.7244204.80417Friday
7411 Jan 21 (09:24:00)11 Jan 21 (09:26:00)PAR$  0.5644207.39167Monday
7511 Jan 21 (09:27:00)11 Jan 21 (09:48:00)PAR$  11.2044207.39375Monday
7611 Jan 21 (09:58:00)11 Jan 21 (10:19:00)PAR$  11.2044207.41528Monday
7711 Jan 21 (11:31:00)11 Jan 21 (11:32:00)PAR$  0.5644207.47986Monday
7811 Jan 21 (11:33:00)11 Jan 21 (11:33:00)PAR$  0.0044207.48125Monday
7911 Jan 21 (12:01:00)11 Jan 21 (12:08:00)PAR$  3.6444207.50069Monday
8011 Jan 21 (13:38:00)11 Jan 21 (13:40:00)PAR$  0.5244207.56806Monday
8111 Jan 21 (16:55:00)11 Jan 21 (17:43:00)PAR$  24.4444207.70486Monday
8211 Jan 21 (17:49:00)11 Jan 21 (17:54:00)PAR$  2.6044207.74236Monday
8311 Jan 21 (17:56:00)11 Jan 21 (18:12:00)PAR$  7.8044207.74722Monday
8411 Jan 21 (18:15:00)11 Jan 21 (18:20:00)PAR$  2.6044207.76042Monday
8511 Jan 21 (18:22:00)11 Jan 21 (18:29:00)PAR$  3.6444207.76528Monday
8611 Jan 21 (18:30:00)11 Jan 21 (18:38:00)PAR$  3.6444207.77083Monday
8711 Jan 21 (18:47:00)11 Jan 21 (18:48:00)PAR$  0.5244207.78264Monday
8811 Jan 21 (18:54:00)11 Jan 21 (19:08:00)PAR$  7.2844207.7875Monday
8911 Jan 21 (19:17:00)11 Jan 21 (19:31:00)PAR$  7.2844207.80347Monday
9011 Jan 21 (19:33:00)11 Jan 21 (19:52:00)PAR$  9.3644207.81458Monday
9111 Jan 21 (20:02:00)11 Jan 21 (20:15:00)PAR$  6.7644207.83472Monday
9211 Jan 21 (20:20:00)11 Jan 21 (20:52:00)PAR$  16.1244207.84722Monday
9311 Jan 21 (20:53:00)11 Jan 21 (20:54:00)PAR$  0.5244207.87014Monday
9411 Jan 21 (21:00:00)11 Jan 21 (21:07:00)PAR$  3.6444207.875Monday
9512 Jan 21 (10:03:00)12 Jan 21 (10:05:00)PAR$  1.1244208.41875Tuesday
9612 Jan 21 (10:06:00)12 Jan 21 (10:33:00)PAR$  15.1244208.42083Tuesday
9712 Jan 21 (11:36:00)12 Jan 21 (11:43:00)PAR$  3.9244208.48333Tuesday
9812 Jan 21 (12:11:00)12 Jan 21 (12:36:00)PAR$  12.4844208.50764Tuesday
9912 Jan 21 (13:32:00)12 Jan 21 (13:34:00)PAR$  1.0444208.56389Tuesday
10012 Jan 21 (16:32:00)12 Jan 21 (16:48:00)PAR$  7.8044208.68889Tuesday
10112 Jan 21 (16:49:00)12 Jan 21 (16:52:00)PAR$  1.0444208.70069Tuesday
10212 Jan 21 (16:55:00)12 Jan 21 (16:55:00)PAR$  0.0044208.70486Tuesday
10312 Jan 21 (16:56:00)12 Jan 21 (17:03:00)PAR$  3.6444208.70556Tuesday
10412 Jan 21 (17:04:00)12 Jan 21 (17:14:00)PAR$  5.2044208.71111Tuesday
10512 Jan 21 (17:32:00)12 Jan 21 (17:40:00)PAR$  3.6444208.73056Tuesday
10612 Jan 21 (17:40:00)12 Jan 21 (17:41:00)PAR$  0.5244208.73611Tuesday
10712 Jan 21 (17:53:00)12 Jan 21 (17:57:00)PAR$  2.0844208.74514Tuesday
10812 Jan 21 (18:15:00)12 Jan 21 (18:27:00)PAR$  6.2444208.76042Tuesday
10912 Jan 21 (18:33:00)12 Jan 21 (18:44:00)PAR$  5.2044208.77292Tuesday
11012 Jan 21 (19:00:00)12 Jan 21 (19:00:00)PAR$  0.0044208.79167Tuesday
11112 Jan 21 (19:15:00)12 Jan 21 (19:17:00)PAR$  0.5244208.80208Tuesday
11212 Jan 21 (19:25:00)12 Jan 21 (19:30:00)PAR$  2.0844208.80903Tuesday
11312 Jan 21 (19:35:00)12 Jan 21 (19:37:00)PAR$  1.0444208.81597Tuesday
11412 Jan 21 (19:41:00)12 Jan 21 (20:05:00)PAR$  11.9644208.82014Tuesday
11512 Jan 21 (20:06:00)12 Jan 21 (20:13:00)PAR$  3.6444208.8375Tuesday
11612 Jan 21 (20:14:00)12 Jan 21 (20:30:00)PAR$  7.8044208.84306Tuesday
11712 Jan 21 (20:32:00)12 Jan 21 (20:39:00)PAR$  3.6444208.85556Tuesday
11812 Jan 21 (20:47:00)12 Jan 21 (21:03:00)PAR$  7.8044208.86597Tuesday
11913 Jan 21 (09:41:00)13 Jan 21 (09:43:00)PAR$  1.1244209.40347Wednesday
12013 Jan 21 (09:52:00)13 Jan 21 (10:35:00)PAR$  23.5244209.41111Wednesday
12113 Jan 21 (11:29:00)13 Jan 21 (11:42:00)PAR$  7.2844209.47847Wednesday
12213 Jan 21 (15:36:00)13 Jan 21 (15:53:00)PAR$  8.8444209.65Wednesday
12313 Jan 21 (16:03:00)13 Jan 21 (16:36:00)PAR$  17.1644209.66875Wednesday
12413 Jan 21 (16:41:00)13 Jan 21 (17:05:00)PAR$  11.9644209.69514Wednesday
12513 Jan 21 (17:27:00)13 Jan 21 (18:01:00)PAR$  17.6844209.72708Wednesday
12613 Jan 21 (18:04:00)13 Jan 21 (18:16:00)PAR$  5.7244209.75278Wednesday
12713 Jan 21 (18:19:00)13 Jan 21 (18:32:00)PAR$  6.7644209.76319Wednesday
12813 Jan 21 (18:36:00)13 Jan 21 (18:36:00)PAR$  0.0044209.775Wednesday
12913 Jan 21 (18:38:00)13 Jan 21 (18:39:00)PAR$  0.5244209.77639Wednesday
13013 Jan 21 (18:41:00)13 Jan 21 (18:43:00)PAR$  1.0444209.77847Wednesday
13113 Jan 21 (18:46:00)13 Jan 21 (19:09:00)PAR$  11.9644209.78194Wednesday
Sheet1
Cell Formulas
RangeFormula
K2:K131K2=C2
L2:L131L2=C2
Named Ranges
NameRefers ToCells
begin=Sheet1!$C$2:$C$1183K2:L2



If you have any ideas, there is a second pint up for grabs. .-)

Many many thanks in advance!
RvL

PS: power query looks like an interesting concept if ever the MAC world gets blessed with it. we are blessed with a no/low virus environment but because of that, we don't get all the icing on the cake (or the cherries).
 

bo_danseuer

New Member
Joined
Oct 23, 2012
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
Haven't figured out a way to combine this, but the helper columns seem to do it.

Book1
GHI
2Mar 20Mar 202
3Mar 20Jan 213
4Jan 21
5Jan 21
6Jan 21
Sheet1
Cell Formulas
RangeFormula
G2:G6G2=RIGHT(UNIQUE(LEFT(C2:C40&D2:D40,9)),6)
H2:H3H2=UNIQUE(RIGHT(G2#,6))
I2:I3I2=COUNTIF($G$2#,H2)
Dynamic array formulas.

kWeaver,
Your option works but it means too much extra work for a database that is updated numerous times throughout the day. One thing does intrigue me in your formulas though. I've never seen formulas with a "#" sign (at least not in that manner). Please can you help me understand this? Or maybe it's something that only works on a PC?

=UNIQUE(RIGHT(G2#,6))
=COUNTIF($G$2#,H2)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
To calculate the numbers of Mondays etc, you can use
+Fluff 1.xlsm
CDEFG
1BeginEndType01/01/2021
202/03/202001/03/2021VVIMon16
301/03/202004/01/2021PARTue12
402/03/202001/03/2021VVIWed7
501/03/202004/01/2021PAR
604/01/202104/01/2021PAR
704/01/202104/01/2021PAR
804/01/202104/01/2021PAR
904/01/202104/01/2021PAR
1004/01/202104/01/2021PAR
1104/01/202104/01/2021PAR
1204/01/202104/01/2021PAR
1304/01/202104/01/2021PAR
1404/01/202104/01/2021PAR
1504/01/202104/01/2021PAR
1604/01/202104/01/2021PAR
1704/01/202104/01/2021PAR
1804/01/202104/01/2021PAR
1904/01/202104/01/2021PAR
2004/01/202104/01/2021PAR
2104/01/202104/01/2021PAR
2205/01/202105/01/2021PAR
2305/01/202105/01/2021PAR
2405/01/202105/01/2021PAR
2505/01/202105/01/2021PAR
2605/01/202105/01/2021PAR
2705/01/202105/01/2021PAR
2805/01/202105/01/2021PAR
2905/01/202105/01/2021PAR
3005/01/202105/01/2021PAR
3105/01/202105/01/2021PAR
3205/01/202105/01/2021PAR
3305/01/202105/01/2021PAR
3406/01/202106/01/2021PAR
3506/01/202106/01/2021PAR
3606/01/202106/01/2021PAR
3706/01/202106/01/2021PAR
3806/01/202106/01/2021PAR
3906/01/202106/01/2021PAR
4006/01/202106/01/2021PAR
Control
Cell Formulas
RangeFormula
G2:G4G2=ROWS(FILTER($C$2:$C$40,(TEXT($C$2:$C$40,"mm/yyyy")=TEXT($G$1,"mm/yyyy"))*(TEXT($C$2:$C$40,"ddd")=F2)))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,798
Members
416,983
Latest member
LessThanAverageUser

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