CODEWITHSUNDEEP
excelfunctiondatetimeformula
Brian
Brian

Reputation: 2286

Excel formula for converting text which contains weeks and days into days

I want everything in days, assuming 1 week = 5 days. So I have the following data (also want to disregard > and < signs):

  1w 3d
> 1w 4d
> 1w 3.25d
< 2w 3.25d
> 1w 0.75d
< 4.25d
  0d

Would like the answer to look like:

8, Calculated like so --> ((1 week * 5) + 3 days)
9
8.25
13.25
5.75
4.25
0

Thank you for your help!

Upvotes: 1

Views: 118

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

For a formula approach:

=IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND("w",A1)-1),">",""),"<","")),0)*5 + TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,IFERROR(FIND("w",A1),0)+1,LEN(A1)),">",""),"<",""),"d",""))

enter image description here

EDIT

As per your comment this formula should deal with the issue.

=IFERROR(TRIM(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND("w",A1)-1),">",""),"<","")),0)*5 + IFERROR(--TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,IFERROR(FIND("w",A1),0)+1,LEN(A1)),">",""),"<",""),"d","")),0)

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Consider the following one-line User Defined Function:

Public Function xvert(sin As String) As Double
   While Not Left(sin, 1) Like "[0-9]"
       sin = Mid(sin, 2)
   Wend
   xvert = Evaluate(Replace(Replace(Replace(sin, " ", "+"), "d", ""), "w", "*5"))
End Function

enter image description here

The UDF works by converting the data into a formula and then evaluating the formula. So:

3w 4.3d

is evaluated as:

=3*5 + 4.3

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Upvotes: 1

Related Questions