LinkBack URL
About LinkBacks
Hi All,
I am trying to count how many people havent logged into a system for 6 months. The date format is "10/01/2014 10:02:37" just cant figure out how to extra a count on date.
Hope you someone can help - see attached.
Hi All,
I am trying to count how many people havent logged into a system for 6 months. The date format is "10/01/2014 10:02:37" just cant figure out how to extra a count on date.
Hope you someone can help - see attached.
All your dates are text and not numbers at all.
in B2
=IF(ISERROR(SEARCH("Logged",A2)),A2+0,0)
and copy down
in B1
=COUNTIF(B2:B585,"<"&TODAY()-180)
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Your 'dates' are stored as text.
So first, let's get them as actual date values: in B2 enter:
Formula:
Please Login or Register to view this content.
and copy down to B3:B585.
Now in Cell B1, we can do the COUNTIF:
Formula:Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Thanks Special-K
Problem is I will need to do this on a different sheet for various number of dates. Do you think its possible to do this in one cell?
As I mentioned to special k I would quite like to do this in one formula as I plan to add other counts if aka if F:F,"Yes"
You can either add helper cells (as we have already suggested), fix your source data, so it is stored as numerical date values, rather than text, or use a bit of VBA.
Can you change the source data? Would you like a VBA solution?
Cant change the source data as that's how IT provide it. So it wouldn't be possible to so something with TEXT like TEXT(DATE(YEAR(TODAY())-6,MONTH(TODAY())-1,DAY(TODAY())),"mm-YYY") ? If not I would love some VBA
Both your formulas appear wrong. The answer should be 115 haven't logged in for over 6 months since 30 June 2014.
You are ignoring the "Never Logged In", then?
Edit: and 30th June is 7 months ago...
Last edited by Olly; 01-30-2015 at 11:56 AM.
=SUMPRODUCT(ISERROR(1/--(A2:A585+180>TODAY()))*1)
try this
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Oh yeah good point they need to count.
How would I go about counting those haven't logged in during the last 6 months but have "Yes" to approved and "Yes" to active? see attached.
Hero. Any idea how I can do that if A and B both = Yes. See the attached
Here's a UDF which does what you want:
Use as:
Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Argument 1 = Lookin = the range of cells containing dates as text.
Argument 2 = Criteria = a criteria string: "<", "<=", "=", ">=" or ">"
Argument 3 = CompareDate = the date to compare against (in this example, 6 months before today)
Argument 4 = CountErrors = if FALSE (or ommitted) error values (such as "Never Logged In") are not counted. If TRUE, they are counted.
Just realised there's a problem with this as the number of dates change when I dump data into the sheet
=SUMPRODUCT((A2:A585="Yes")*(B2:B585="Yes")*ISERROR(1/--(C2:C585+180>TODAY())))
try this
I'm too much of a noob I couldnt get this work. I guess I need a formula that will do this for a whole column.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks