Link to home
Start Free TrialLog in
Avatar of jskfan
jskfanFlag for Cyprus

asked on

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
Avatar of Norie
Norie

Can you attach a sample workbook with some examples?
You may try something like this...

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

Open in new window


User generated image
Alternatively shorter formula would be:
=B2-TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))

Open in new window

Assuming you have Duration in Col A, End Date & Time in Col B and you want result in Col C:
 User generated image
Avatar of jskfan

ASKER

=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
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.
Avatar of jskfan

ASKER

Here is the Snapshot
User generated image
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)

Open in new window


User generated image
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jskfan

ASKER

:Subodh Tiwari (Neeraj)

It worked,

Thank you for your Help
Avatar of jskfan

ASKER

Thank you
You're welcome jskfan! Glad it worked as desired.