 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I need to count the number of instances in a date range that are >1yr, 2yr, 3yr etc. i.e. between today and 1yr ago, between 1yr and 2yr ago and so on. I'm pretty sure it's a COUNTIFS or COUNTIF formula and using the TODAY() function but I can't get mine to work properly. Any suggestions?
i'd use system date minus date to get days, then code it from there
=today()-datecol = fieldX
then you could do an if formulae ie =if(fieldX<365,"year1",if(fieldX<730,"Year2","year3etc"))) ... etc
to group the records under each heading year1 , year2 etc
Dates in col A
Col B formula =TODAY()-A1
Copy down
Then =countif(B1:B99, ">365")
There should be a single-cell way of doing it like countif(A1:A99, ">TODAY()-365") but that syntax doesn't seem to work so I just do it the less elegant way with another column.
Why do I waste time on these little puzzles? Anyway here you go:
=COUNTIF(A1:A99, ">"&TODAY()-365)
P.S. You may find that the above formula doesn't copy into Excel properly because it changes the quotation marks. If that happens, type it out again directly into Excel.
damn excel and accountants, a Database with proper SQL is the way
Are you all handling leap years OK?
How about this for a long winded answer where the dates are in column A
Past year
=COUNTIFS(A:A,">"&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),A:A,"<="&TODAY())
One to two years ago
=COUNTIFS(A:A,">"&DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())),A:A,"<="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))
damn excel and accountants, a Database with proper SQL is the way
Not far from the truth there. The figures should have been produced by head office but for some reason aren't. This leaves muggins here faffing around with Excel formulas.
Cheers all. I've managed to get a working formula, albeit one that ignores leap years but the data won't need to be accurate to that detail so I'll figure that out further down the line.