jskfan
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
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
Can you attach a sample workbook with some examples?
ASKER
=IFERROR(EDATE(B2,-LEFT(A2 ,FIND(" ",A2)-1)*IF(ISNUMBER(SEARC H("year",A 2)),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
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
:Subodh Tiwari (Neeraj)
It worked,
Thank you for your Help
It worked,
Thank you for your Help
ASKER
Thank you
You're welcome jskfan! Glad it worked as desired.