Calculate Expired Date with T in Date Format

[Vgabond]

Hi MrExcel Guru

Seeking for your assistance as I have more than 100 csv files in a folder that I need to do the following :
1. Count row that have Expired Date. Date format is YYYY-MM-DDTHH:MM:SS. Then write the count of rows ( expired date) in a new sheet : Summary Expired Date
2. Write the file name in sheet: Summary Expired Date

Any assistance is appreciated

 

[Vgabond]

Hi MrExcel Guru

Seeking for your assistance as I have more than 100 csv files in a folder that I need to do the following :
1. Count row that have Expired Date. Date format is YYYY-MM-DDTHH:MM:SS. Then write the count of rows ( expired date) in a new sheet : Summary Expired Date
2. Write the file name in sheet: Summary Expired Date

Any assistance is appreciated

To add on. The expired date is in Column O. Any help is appreciated

 

[RoryA]

I'd suggest you use Power Query as it was built for this kind of thing.

 

[Vgabond]

Thank your for reply. Can you elaborate more on how to achieve that

I'd suggest you use Power Query as it was built for this kind of thing

 

[RoryA]

Which version of Excel do you have?

The UI varies quite a bit in different versions, but the basic instructions to load multiple files from a folder are here: Import data from a folder with multiple files (Power Query) - Microsoft Support

Is the expired date column blank for rows that haven't expired, or does it have some specific value in it?

 

[Vgabond]

Which version of Excel do you have?

The UI varies quite a bit in different versions, but the basic instructions to load multiple files from a folder are here: Import data from a folder with multiple files (Power Query) - Microsoft Support

Is the expired date column blank for rows that haven't expired, or does it have some specific value in it?

Hi RoryA

I am using Excel 365, and there is a value present in every row.

 

[RoryA]

If there's a value for every row, does that mean that all the data is expired and you basically just want a row count from each file?

 

[Vgabond]

If there's a value for every row, does that mean that all the data is expired and you basically just want a row count from each file?

It should count the rows with expired dates and then list the file name and the count of expired rows on the 'Summary Expired Date' sheet.

 

[RoryA]

By 'expired date' do you just mean any date in that column that is less than the day the query is run?

 

[Vgabond]

By 'expired date' do you just mean any date in that column that is less than the day the query is run?

Yes. Any date in the column O that is less than the day the query is run.