Welcome back to Twice Daily Dose of Excel. Heck, these days we’re more regular than Julian Assange’s visits to the Ecuadorian Embassy!
Audrey has a table that looks something like this (if you’re American):
…or like this (if you live somewhere sensible, like in New Zealand and a good deal many other places besides):
She uses this to track when she requested a piece of information from someone, and the date she received a response. I’m picking she’s NSA, and is asking Julian – who is actually a quadruple agent – whether he needs more sun lamps. Stranger things have happened, recently.
Anyways, she’d like a formula to find the oldest/latest request date out of all responses received in a particular month.
She could array enter something like this, which will do the job without any need for helper columns:
=MAX($A$2:$A$20*(DATE(YEAR($B$2:$B$20),MONTH($B$2:$B$20),1)=DATE(YEAR($B2),MONTH($B2),1)))
…which to an Excel Pro with a lifetime of formulas under their belt would look like this:
=NOT(RocketScience)
…but to anyone else:
="Αυτό θα μπορούσε κάλλιστα να είναι γραμμένο στην ελληνική γλώσσα"
You could debate whether there’s a right formula to use in a situation like this. In fact there’s some great debate on that original blogpost as to whether one proposed solution is awesome, potentially obfuscating, or incomprehensibly mutant. So with this in mind, is there a right formula to use in this case? Depends on who’s trying to comprehend what’s happening here in 6 months time. Perhaps yourself, with six more months of grey-matter dieback under your belt. Or hat, rather. Is there a right non-formula approach to Audrey’s problem? You betcha:
Just create a PivotTable out of that sucker:
…drag the ‘Date Received’ field to the Rows pane and the ‘Date Requested’ field to the Values pane:
…launch the Value Field Settings dialog for the Date Requested field:
…change the name of the Date Received field to something meaningful, and change Sum to Max:
…plus click on that Number Format button while you’re there so you can change the format to Date:
…then select any cell in the Date Received column and click Group Selection from the PivotTable Tools > Analyze contextual tab, and group by Months and Years:
…and exhale:
No, wait…hold that breath…we forgot to change the name of that ‘Years’ column that just appeared – as well as the ‘Date Received’ column that now holds Months only – to something more suitable:
Now exhale.
Here’s the genius…it even works in American:
…and it works on trickier problems, like the original one at that post:
¿Fórmulas? No nos hacen falta fórmulas apestosas!
I’, with you Jeff. Having a good understanding of formulas is important, but this is a much better approach. With formulas, if you need to answer a different question, you’ll likely have to write a whole new set of formulas. With a pivot table, just pivot the parts around a bit and you have your answer.
Uh oh, the Spanish is coming out. Sounds like wifey is back.
Heck no. If she was back, I’d be muertos on account of the strong inverse relationship between blogging and housework. Luckily I’ve got a week left of bachelorhood. I’ill need it, given the house is covered in a perceptible layer of grime, not to mention an invisible coating of what I’m told females call dust which apparently is undesirable. My posting schedule may fall as the hygiene level rises. I’ll whip up a graph.
No, rather I’m brushing up on my Spanish so I can visit Ecuador. Julian must have some mean tips on how to handle and distribute large amounts of data.
One of these days I’m going to have a good look at these new fangled pivot-table things.
in the meantime, I offer this nifty little UDF, complete with code tags (which doesn’t even need to be entered as an array function):
Function Earliest_in_month(RMonth As Long, DataRange As Variant) As Double
Dim i As Long, EiM As Long, Nummatch As Long
DataRange = DataRange.Value2
For i = 1 To UBound(DataRange)
If DataRange(i, 1) = "" Then Exit For
If month(DataRange(i, 2)) = RMonth Then
Nummatch = Nummatch + 1
If Nummatch = 1 Then
EiM = DataRange(i, 1)
Else
If DataRange(i, 1) < EiM Then EiM = DataRange(i, 1) End If End If Next i Earliest_in_month = EiM End Function
Hey Jeff, I know this is going a ways back, but I just wanted to point out something.
The first step after making the pivot table states, “…drag the ‘Date Requested’ field to the Rows pane and the ‘Date Received’ to the Values pane:” . But according to the picture i believe it should be vice versa.
Anyways thanks for the helpful tip!
TD
Thanks Tyler. Fixed.