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

 

[Andrew Poulsom]

Welcome to MrExcel.

It's difficult to tell what's in what column there. Can you try surrounding your data with borders and reposting please?

 

[loto]

Hi there...I've uploaded it to Google docs in case you can't see the data properly...
https://docs.google.com/spreadsheet/ccc?key=0Avu18NHHmONKdE8taTlZY01pMFJKTDVYZktlUFpMcmc

<table width="467" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:4022; width:83pt" span="2" width="110"> <col style="mso-width-source:userset;mso-width-alt:4352;width:89pt" width="119"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:48pt" height="17" width="64">User</td> <td class="xl24" style="border-left:none;width:48pt" width="64"> </td> <td class="xl24" style="border-left:none;width:83pt" width="110">Member for</td> <td class="xl24" style="border-left:none;width:83pt" width="110"> </td> <td class="xl24" style="border-left:none;width:89pt" width="119">Last access

</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">user 1</td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl26" style="border-top:none;border-left:none">2 years 20 weeks</td> <td class="xl26" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none">54 sec ago</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">user 2</td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl26" style="border-top:none;border-left:none">44 min 7 sec</td> <td class="xl26" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none">1 min 37 sec ago</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">user 3</td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl26" style="border-top:none;border-left:none">49 weeks 2 days</td> <td class="xl26" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none">17 min 3 sec ago</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">user 4</td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl26" style="border-top:none;border-left:none">5 weeks 2 days</td> <td class="xl26" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none">33 min 32 sec ago</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">user 5</td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl26" style="border-top:none;border-left:none">38 min 9 sec</td> <td class="xl26" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none">38 min 9 sec ago</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">user 6</td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl26" style="border-top:none;border-left:none">5 weeks 3 days</td> <td class="xl26" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none">1 hour 7 min ago</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">user 7</td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl26" style="border-top:none;border-left:none">2 hours 17 min</td> <td class="xl26" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none">2 hours 11 min ago</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17">user 8</td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl26" style="border-top:none;border-left:none">15 weeks 1 day</td> <td class="xl26" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none">3 hours 13 min ago</td> </tr> </tbody></table>

Thanks!

 

[Andrew Poulsom]

To convert the entry in C2 to a serial time:

=(IF(ISERROR(FIND("hour",C2)),"0:","")&IF(ISERROR(FIND("min",C2)),"0:","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"ago",""),"hours","hour")," hour ",":")," min ",":")," sec ","")&IF(ISERROR(FIND("sec",C2)),"0",""))+0

formatted as hh:mm:ss.

 

[loto]

Thanks Andrew...you're clearly a genius .... that's resolved column c nicely. If I want to also convert the dates in column b to 'weeks from today's date' - can I use a similar approach but format the data differently? Any ideas? e.g. I would like to convert 2 years 20 weeks ago to x number of weeks from 17/12/11, e.g. giving me approximately 124 weeks.

Thanks again.

 

[GopherUK]

I haven't had a chance to double-check this, so take it as a UAT version and have a play around with it. It calculates the number of months from the date data given in column B, then uses EDATE() to subtract the number of months from TODAY().

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	User	Member for		Last access	YEARS	MONTHS	WEEKS	DAYS	HOURS	MINS	SECS	YEARS	MONTHS	WEEKS	DAYS	HOURS	MINS	TOTAL	INITIAL DATE
2	user 1	2 years 20 weeks	2 YEARS 20 WEEKS	54 sec ago	2		20					24	0	4.6	0	0	0	28.6	17/08/2009
Sheet7

http://www.excel-jeanie-html.de/index.php?f=1

 

[Andrew Poulsom]

Try:

=IF(ISNUMBER(FIND("min",B2)),0,IF(ISNUMBER(FIND("year",B2)),LEFT(B2,FIND(" year",B2)-1)*52,0)+IF(ISNUMBER(FIND("week",B2)),IF(ISNUMBER(FIND("year",B2)),MID(B2,FIND(" week",B2)-2,2),LEFT(B2,FIND(" week",B2)-1)),0))

 

[GopherUK]

This is an improvement on the first one... gets it down to the minute.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	User	Member for		Last access	YEARS	MONTHS	WEEKS	DAYS	HOURS	MINS	SECS	YEARS	MONTHS	WEEKS	DAYS	HOURS	MINS	TOTAL	INITIAL DATE
2	user 1	2 years 20 weeks	2 YEARS 20 WEEKS	54 sec ago	2		20					730	0	140	0	0	0	870	30/07/2009 18:14
DateExtraction2

 

[GopherUK]

The only definite inaccuracy in that is that it assumes 365 as the number of days in a year (might is slightly inaccurate, but probably not consequential for this matter).

I will have to double-check the MID formulas to make sure they are definitely extracting the correct values from the initial text.

 

[GopherUK]

This should give you join date, last use date and number of days from join date to present.

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 20 WEEKS	2		20					730	0	140	0	0	0	54 SECS AGO							54	0	0	0	0	0	0	30/07/2009 18:43	17/12/2011 18:43	870
DateExtraction2

http://www.excel-jeanie-html.de/index.php?f=1