# Excel Formula

Excel Formula

I have 2 columns in Excel, one is named "Duration" and the Data in it is for instance: 2 years, 1 year, 3 Months,etc... and another column named "End Date and Time". it has normal Date and Time Value , ex: 12/18/2018 19:00

I would like to add a Column named "Start Date and Time" which will have the value of "End Date and Time" - "Duration". ( "End Date and Time"  minus "Duration")
As you can see the "Duration" column I believe needs somehow to be in a different Conversion Columns for Excel to do the substraction.
Example

1 year   365
1 Month  30
2 Months 60
...

Any Help ?

Thank you
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Analyst Assistant Commented:
Can you attach a sample workbook with some examples?
Excel & VBA ExpertCommented:
You may try something like this...

``````=IFERROR(EDATE(B2,-LEFT(A2,FIND(" ",A2)-1)*IF(ISNUMBER(SEARCH("year",A2)),12,1))++MOD(B2,1),"")
``````

Excel & VBA ExpertCommented:
Alternatively shorter formula would be:
``````=B2-TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))
``````
Assuming you have Duration in Col A, End Date & Time in Col B and you want result in Col C:

Author Commented:
=IFERROR(EDATE(B2,-LEFT(A2,FIND(" ",A2)-1)*IF(ISNUMBER(SEARCH("year",A2)),12,1))++MOD(B2,1),"")

it worked for me , For Year and Month, but I have weeks too. It gave wrong start date for Weeks
Excel & VBA ExpertCommented:
The formula was suggested considering your description.
So it's always better to upload a sample workbook (not the image) with some dummy data along with the desired output mocked up manually.
Author Commented:
Here is the Snapshot
Excel & VBA ExpertCommented:
Okay try this...

In C2
``````=CHOOSE(MATCH(MID(A2,FIND(" ",A2)+1,3),{"yea","mon","wee","day"},0),EDATE(B2,-LEFT(A2,FIND(" ",A2)-1)*12),EDATE(B2,-LEFT(A2,FIND(" ",A2)-1)*1),B2-LEFT(A2,FIND(" ",A2)-1)*7,B2-LEFT(A2,FIND(" ",A2)-1)*1)+MOD(B2,1)
``````

Excel & VBA ExpertCommented:
It would be better to wrap this formula with IFERROR like below...

``````=IFERROR(CHOOSE(MATCH(MID(A2,FIND(" ",A2)+1,3),{"yea","mon","wee","day"},0),EDATE(B2,-LEFT(A2,FIND(" ",A2)-1)*12),EDATE(B2,-LEFT(A2,FIND(" ",A2)-1)*1),B2-LEFT(A2,FIND(" ",A2)-1)*7,B2-LEFT(A2,FIND(" ",A2)-1)*1)+MOD(B2,1),"")
``````

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
:Subodh Tiwari (Neeraj)

It worked,