Excel: Find number of unique dates in a row

Question

I have an excel table with a list of dates like this:

12.12.2022 20:13
12.12.2022 20:30
12.12.2022 21:06
12.12.2022 21:55
12.12.2022 22:03

14.12.2022 18:24
14.12.2022 19:24

15.12.2022 17:45
15.12.2022 18:55
15.12.2022 20:03

01.01.2023 20:04

The list contains values for different years and not all days have values. I want to get the number of days with existing list entries in 2022. So for the example list the result should be 3, because the last value es already 2023.

I guess it must work with UNIQUE somehow, but I don't know how. The date field is formatted like this: TT.MM.JJJJ hh:mm

The value is: 14.12.2022 19:24:00

Thank you for your help!

Jos Woolley · Accepted Answer · 2023-01-07 12:56:58Z

5

With O365:

=LET(ζ,A1:A14,ROWS(UNIQUE(FILTER(INT(ζ),YEAR(ζ)=2022))))

answered Jan 7, 2023 at 12:56

Jos Woolley

9,1322 gold badges7 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user3549835 Over a year ago

Not working with O365 Excel 2211. I doesn't recognize the symbol that is the first variable to LET

WeAreOne Over a year ago

@user3549835 change that symbol to a letter or word you like

user3549835 Over a year ago

Thank you. It works. Just for info: Why do I need the rows function?

Jos Woolley Over a year ago

ROWS is one option to count the number of entries returned by UNIQUE.

user3549835 Over a year ago

Is there a way by any chance to modify the function so that a new day starts at 6 AM? e.g. an entry on 02 JAN 2023 05:00 AM should be accounted as an entry on the 1st of JAN

|

Collectives™ on Stack Overflow

Excel: Find number of unique dates in a row

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related