My Online Training Hub

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

Help - need to convert data labelled as 1 week, 2 months...|General Excel Questions & Answers|Excel Forum|My Online Training Hub

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
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: 415
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: Kevin Campbell
Guest(s) 6
Currently Browsing this Page:
1 Guest(s)
Top Posters:
Catalin Bombea: 1939
SunnyKow: 1432
Anders Sehlstedt: 930
Purfleet: 415
Hans Hallebeek: 354
Frans Visser: 349
David_Ng: 312
lea cohen: 248
Jessica Stewart: 219
A.Maurizio: 216
Newest Members:
QkBXoylJZ ionivxwYdRFNFL
kEUeirrVvEo LuuDySmTmIizW
uavBfwotTrMPA NIQkZzprwMG
qVMPnaSzpgZ uaHBwyav
rCkwbAORdf eaYibAkYjfXT
eNvhcbgYvaPRp nImfgnjOk
kwKbBpnpPjgKuhr LSOUORDzhcRqQ
CPUXMYKMkLuXs iAsOmBtNLn
Clive Webb
EXerOrqW PRqmMlMeyNd
Forum Stats:
Groups: 3
Forums: 25
Topics: 7095
Posts: 31105

 

Member Stats:
Guest Posters: 49
Members: 34537
Moderators: 2
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Jessica
Moderators: Velouria, Riny van Eekelen