Formula to convert human-readable text strings into a consistent number of weeks from today's date?

[loto]

Hi there, is it possible to do the following with a formula in Excel...

I have a list of users with the dates they first logged into a system and the date they last logged in. I'm trying to group them together into segments so I can analyse them using a Pivot table and chart to see how often different groups are using the system, e.g. New customers in December, November, October, etc.

The trouble I am having is trying to convert the different human-readable text strings into a consistent number of weeks from today's date, e.g.

USER FIRST LOGGED IN LAST LOGGED IN
user 1 2 years 20 weeks 54 sec ago
user 2 44 min 7 sec 1 min 37 sec ago
user 3 49 weeks 2 days 17 min 3 sec ago
user 4 5 weeks 2 days 33 min 32 sec ago
user 5 38 min 9 sec 38 min 9 sec ago
user 6 5 weeks 3 days 1 hour 7 min ago
user 7 2 hours 17 min 2 hours 11 min ago
user 8 45 seconds ago 45 seconds ago

Is there a formula I can use to convert these human readble text strings into a number of weeks elapsed since today's date?

Thanks

loto

 

[loto]

@Andrew - thank-you so much that formula works perfectly and converts everything beautifully into weeks.

@Gopher - thank-you so much for such a comprehensive answer - it looks like that would be really useful - but for some reason Excel tells me I have an error when I try and paste the long TRIM formula in cell C2. I've broken it down to its constituent parts and started entering the formula bit by bit from the centre outwards if this makes sense. It works up until the point you add the second UPPER, and if you ignore it then it gives you an error when you add in the TRIM. I can see from your s/s that you got it working. But the other formulas that look at C2 show me #NAME? ....any ideas what I am doing wrong? I have all the column names correct....?

 

[barry houdini]

This formula should also give you total weeks

=SUM((0&MID(0&B2,FIND({"year","week"},B2&"xxxyearweek")-2,2))*{52,1})

 

[GopherUK]

@Andrew - thank-you so much that formula works perfectly and converts everything beautifully into weeks.

@Gopher - thank-you so much for such a comprehensive answer - it looks like that would be really useful - but for some reason Excel tells me I have an error when I try and paste the long TRIM formula in cell C2. I've broken it down to its constituent parts and started entering the formula bit by bit from the centre outwards if this makes sense. It works up until the point you add the second UPPER, and if you ignore it then it gives you an error when you add in the TRIM. I can see from your s/s that you got it working. But the other formulas that look at C2 show me #NAME? ....any ideas what I am doing wrong? I have all the column names correct....?

Which version of Excel are you using?

 

[loto]

2003 unfortunately

 

[GopherUK]

Only 7 levels of nesting allowed in 2003

 

[loto]

OK....time for an upgrade then - thanks again

 

[GopherUK]

No probs.

BTW, I noticed an error in one of the SUBSTITUTE formulas. Use this instead: -

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	User	Member for	Last access	Member for (cleaned)	YEARS	MONTHS	WEEKS	DAYS	HOURS	MINS	SECS	YEARS	MONTHS	WEEKS	DAYS	HOURS	MINS	Last access (cleaned)	YEARS	MONTHS	WEEKS	DAYS	HOURS	MINS	SECS	YEARS	MONTHS	WEEKS	DAYS	HOURS	MINS	JOIN DATE	LAST ACTIVE	TOTAL DAYS
2	user 1	2 years 20 weeks	54 sec ago	2 YEARS S 20 WEEKS	2		20					730	0	140	0	0	0	54 SECS AGO							54	0	0	0	0	0	0	31/07/2009 16:00	18/12/2011 16:00	870
DateExtraction2

 

[Andrew Poulsom]

This formula should also give you total weeks

=SUM((0&MID(0&B2,FIND({"year","week"},B2&"xxxyearweek")-2,2))*{52,1})

Good Lord!

 

[GopherUK]

That is what I thought