Return earliest/latest date/time to utilize in pivot table If/Index/Match/MIN Functions

I'm not sure if I followed your explanation. Check if such array formula * in C2 and copied down gets you closer to required result:

Formula:

Please Login or Register  to view this content.

*) confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

I hope an Excel expert can assist to create a correct formula. The sample data is attached.

The ultimate goal is for pivot table (Pivot tab in the file) to display date/time stamp when first review was submitted for the corresponding campaign ID. I inserted a column 'C' ("Date & Time for Deployed?") in "Data" tab where I want to insert the formula which:

returns in column 'C' the earliest submission date/time from column 'B' ("Submission Date & Time") for for corresponding value in column 'H' ("Deployed?") but don't return any value if there is no value in column 'H' and if the date/time is not the earliest.

Thus the earliest date/time stamp would be displayed in pivot table below the each campaign ID for 5 Day email and for 28 Day email when "Date & Time for Deployed?" field is added to 'Row Labels' in the pivot table.

For example, '5day_latency_RZ040815' campaign ID from "Deployed?" column has total of two date/time stamps in column B ("Submission Date & Time"): 4/8/15 8:55:43 AM and 4/8/15 8:25:04 AM. Note, that the actual data is larger than the sample I provided and for one campaign ID can be hundredth date/time stamps.

The earliest date that I need to be displayed in the pivot table is 4/8/15 8:25:04 AM, it is when the first review submissions was made, meaning that 5 Day email was deployed.

I am trying to use IFERROR/IF/Index/Match/Min functions but the formula doesn't work: =IFERROR(INDEX(H:H,MATCH(MIN(B:B,0)),"")

The purpose of formula in column 'H' is narrow down the campaign IDs from column 'K' to the ones that are appended with today's date. I need to verify that today these campaign IDs were deployed.

And the purpose of formula in column 'C' is to get the earliest review submission time, thus validating the time that campaign IDs were deployed and display the time that it was deployed.



Thanks so much!!!!!

Here you go. DATA_SAMPLE.xlsx

ups, sorry. little mistake

Perhaps this ARRAY formula...
=MIN(IF($K$2:$K$545=K2,$B$2:$B$545))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Neyme, please stop posting duplicate threads. This the third thread on this same question. I have closed 1 (with no responses), and will merge this 1 with the other that also has replies on it

I really need to get an quick answer and a solution to this problem. I noticed that new threads get an answer quicker. Sorry about that. I'll stop duplicate posting. I hoped that you will address my question since I had a great experience with your prompt and elegant solutions.

Your formula returns correct MIN date/time (earliest date/time)in column 'C' for values in column 'H'

but I need to have blank cells in column'C' if the corresponding row cell in column 'H' is blank.

In addition, can the formula utilize only columns 'B', 'C' and 'H' and do not use columns 'I' through 'L'.

The purpose of formula in column 'H' is to narrow down the campaign IDs from column 'K' to the ones that are appended with today's date. I need to verify that today these campaign IDs were deployed.

And the purpose of formula in column 'C' is to get the earliest review submission time, thus validating the time that campaign IDs were deployed and display the time that it was deployed. So the pivot table will show only the submission date/time for today's campaign IDs in column 'H'. I hope it helps to better understand what I need.

Can it be done by modifying your formula?


Need Return Blank Cells.PNG

Thanks so much!

All advice and formula provided so far do not work. I am trying to create a formula which would:

> return the smallest, earliest date/time from column 'B' for a value in column 'H'. The formula should be in column'C'. If the cell in column 'H' is blank, then the formula should return nothing (stay blank) in column 'C'.

I am still trying to figure it out but get the #VALUE error. See the screenshot below and the attached file (Weekend Email Health Check) from which I made the screenshot. The formula I am using is:

=IF(NOT(ISBLANK(H2)),(MIN(IF($H$2:$H$2561,$B$2:$B$2561,""))))

Weekend Email Healthcheck1.xlsx

Screenshot.png