Start date from end date and text string help

[b19upj]

Hi all.

TIA for viewing/helping

If column A1 I need to find a start date
B1 is an end date in the following format: 02/03/2014
C1 is a duration of employment typed in the following format: 11 yrs 6 months

How can i get a start date (Column A) based on the column B and C data.

Many thanks

 

[BarryL]

Hi,

I would imagine you will have to add another column in to calculate the number of days in your piece of text there. For example based off your current format where C1= "11 yrs 6 months" in D1 i have =(LEFT(C1,2)*365)+(MID(C1,8,2)*30).
This is an estimate obviously. Then in A1 i have "=B1-D1".

If you need 100% accuracy i would be out of ideas.

Let me know.

 

[steve the fish]

This works with the proviso that your data is held exactly as you have stated (number then space then "yrs" then space then another number then "months".

=DATE(YEAR(B1)-LEFT(C1,SEARCH("yrs",C1)-1)+0,MONTH(B1)-MID(C1,SEARCH("yrs",C1)+4,SEARCH("months",C1)-SEARCH("yrs",C1)-4)+0,DAY(B1))

 

[skywriter]

Edit: Looks like steve the fish beat me to it.


Here's a solution with the years and months in separate columns. If your years and months are in one cell and entered as numbers and text together that would require a different formula to extract those numbers.
Here's this one if you care to use it.

Excel 2010

	A	B	C	D

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]End Date[/TD]
[TD="align: center"]Years[/TD]
[TD="align: center"]Months[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]August 4, 2002[/TD]
[TD="align: center"]February 3, 2014[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]6[/TD]

</tbody>

Sheet15​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=B2-365.25*C2-D2/12*365.25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Edit: Looks like steve the fish beat me to it.


Here's a solution with the years and months in separate columns. If your years and months are in one cell and entered as numbers and text together that would require a different formula to extract those numbers.
Here's this one if you care to use it.

Excel 2010

	A	B	C	D

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]End Date[/TD]
[TD="align: center"]Years[/TD]
[TD="align: center"]Months[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]August 4 , 2002[/TD]
[TD="align: center"]February 3 , 2014[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]6[/TD]

</tbody>

Sheet15
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=B2-365.25*C2-D2/12*365.25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

If only months and years are being subtracted, why did the day number change?

I would think if the months and years were separated like you show, that this formula would do what the OP wanted...

=DATE(YEAR(B2)-C2,MONTH(B2)-D2,DAY(B2))

 

[skywriter]

If only months and years are being subtracted, why did the day number change?

I would think if the months and years were separated like you show, that this formula would do what the OP wanted...

=DATE(YEAR(B2)-C2,MONTH(B2)-D2,DAY(B2))

I don't know what the expected result is, nor do I know where the 6 months came from. Whether it's an exact number, rounded up rounded down etc. and what is six months? 180 days? 365/5? 365.25/5?

 

[ukmikeb]

Hi

Try :-

=EDATE(B1,-((FIND("yrs",C1)>0)*LEFT(C1,FIND("yrs",C1)-2)*12+(FIND("months",C1)>0)*MID(C1,FIND(" months",C1)-2,2)))

hth

 

[Rick Rothstein]

C1 is a duration of employment typed in the following format: 11 yrs 6 months

Is the word years really abbreviated "yrs" while the word months is fully spelled out?

Is that always going to be the case?

What about for 1 year... is it still "yrs" or does the abbreviation change to "yr"?

What about for 1 month... is it still "months" or does it change to "month"?


NOTE
------------
Using two columns, one for years and the other for months (as skywriter shows in Message #4 make more sense and would be easier to work with.

 

[b19upj]

No, unfortunately years is spelt inconsistently throughout the spreadsheet (yr/yrs/years/year etc). Im glad this has provided some areas of discussion though.
I cheated a little... My solution was to get my PA to separate the years into a new column (D) and the months into another (E) manually, then i wrote the following formula: A=((Dx365)+(Ex30))-B
It gave me a date which looks correct.


Thanks

(Feel free to keep discussing because there must be a better/more time efficient way than manually opening out the time to two columns as i did!)

Is the word years really abbreviated "yrs" while the word months is fully spelled out?

Is that always going to be the case?

What about for 1 year... is it still "yrs" or does the abbreviation change to "yr"?

What about for 1 month... is it still "months" or does it change to "month"?


NOTE
------------
Using two columns, one for years and the other for months (as skywriter shows in Message #4 make more sense and would be easier to work with.

 

[ukmikeb]

My solution was to get my PA to separate the years into a new column (D) and the months into another (E) manually, then i wrote the following formula: A=((Dx365)+(Ex30))-B
It gave me a date which looks correct.

Then :-

=EDATE(B1,-(D1*12+E1))

should give you the correct date.

hth