My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Help - need to convert data labelled as 1 week, 2 months...|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope



Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_PrintTopic sp_TopicIcon
Help - need to convert data labelled as 1 week, 2 months...
Avatar
Mike D

New Member
Members
Level 0
Forum Posts: 2
Member Since:
December 31, 2019
sp_UserOfflineSmall Offline
1
December 31, 2019 - 10:03 pm
sp_Permalink sp_Print

Hey there

Would REALLY appreciate some help with this. I am trying to extrapolate some sales projections from data which requires converting a column which contains a time period. The issue is that the time periods are mixed, eg 1 week, 3 months, 17 weeks, 23 months etc. 

What's the fastest way to convert this column to something I can then use in a formula? 

This is an urgent query. THANKYOU! 

 

Sales Item Time period
160 chair 1 week
64 Chair 1 month
224 Sofa 2 months
352 Lamp 3 months
128 Table 1 week
64 Bench 2 months
160 Table 5 months
128 chair 2 weeks
256 Chair 3 weeks
288 Sofa 4 weeks
96 Lamp 5 weeks
128 Table 6 weeks
352 Bench 1 month
0 Table 2 months
224 Lamp 3 months
64 Table 1 week
32 Bench 2 months
160 Table 5 months
256 chair 1 month
64 Chair 2 months
256 Sofa 3 months
224 Lamp 1 week
160 Table 2 months
64 Bench 5 months
96 Table 1 month
192 chair 2 months
288 Chair 3 months
128 Sofa 1 week
sp_AnswersTopicSeeAnswer See Answer
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
December 31, 2019 - 11:10 pm
sp_Permalink sp_Print sp_EditHistory

How about this in column D, gives you the number of days

=IF(ISNUMBER(SEARCH("Week",C8))=TRUE,LEFT(C8,2)*7,IF(ISNUMBER(SEARCH("Month",C8))=TRUE,LEFT(C8,2)*30,""))

Weeks is fairly easy, but months you would need to work out the number of days 28, 30 or 31 - you can change the 30 at the end

Let me know if it suits

sp_AnswersTopicAnswer
Answers Post
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
3
January 1, 2020 - 12:28 am
sp_Permalink sp_Print

Possibly another try is

=IF(ISNUMBER(SEARCH("week",C2)),7,30)*LEFT(C2,2)

As mentioned above by Purfleet, the no. of days in a month may be between 28 to 31 days.

Avatar
Mike D

New Member
Members
Level 0
Forum Posts: 2
Member Since:
December 31, 2019
sp_UserOfflineSmall Offline
4
January 2, 2020 - 9:27 pm
sp_Permalink sp_Print

Thanks both so much. In the end I split the data using text to columns and then used a more simple formula and assumed every month is 30 days but your ways look more accurate. Thanks! 

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, Ben Hughes, Alison West, Jill Niemeier, Laxmi Praveen, Francis Drouillard
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6201
Posts: 27185

 

Member Stats:
Guest Posters: 49
Members: 31861
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen