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 |
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
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.
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!
