Convert text to number of days
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Convert text to number of days

  1. #1
    New Member
    Join Date
    Jul 2012
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Convert text to number of days

     
    I need a way to convert a text field to number of days. Can this be done easily?

    The entries look like this:

    1 year 3 weeks
    1 month 2 days
    2 years 4 months
    3 weeks 1 day
    2 years 7 months
    1 week 3 days
    3 months 1 week

    etc.

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    31,879
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Convert text to number of days

    Quote Originally Posted by erahi View Post
    I need a way to convert a text field to number of days. Can this be done easily?

    The entries look like this:

    1 year 3 weeks
    1 month 2 days
    2 years 4 months
    3 weeks 1 day
    2 years 7 months
    1 week 3 days
    3 months 1 week

    etc.
    The problem with your request is you have months in there... that makes the answer dependent on the starting starting date given that the number of days within the months which, in turn, means there is no solution to your problem given the information you have provided.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular
    Join Date
    Jun 2017
    Location
    Nth Vic, Australia
    Posts
    593
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Convert text to number of days

      
    Quote Originally Posted by erahi View Post
    I need a way to convert a text field to number of days. Can this be done easily?

    The entries look like this:

    1 year 3 weeks
    1 month 2 days
    2 years 4 months
    3 weeks 1 day
    2 years 7 months
    1 week 3 days
    3 months 1 week

    etc.
    Nice question ("frowning")
    Easily?

    I have a few questions of my own.
    How important is it to know which year? Leap years will influence reporting? Does your sheet have a column to report that?
    Assumedly the Year will begin from 1 January?
    Similarly the length of months can be 28,29,30 or 31. Therefore, when dealing with one month, can that be detected? With several months can the start month be detected?
    Again, I assume that a week will be from Sunday, and therefore 7 days.

    Depending upon your answers I'm thinking that a macro would be the only way. That said, if your sheet had columns like:
    Start Year, Start Month, someone might be able to produce a formula which could be copied down your No. of Days column.

    Hope that gives some guidance.
    You may see and know but until you tell me exactly I am blind; I cannot read your mind.
    Attachments? No, but check for on site preferences.
    Cloud sharing or other off-site links? Yes, but off-site links are fragile into the future.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com