Combining formulas into a single cell

[SSawyer]

Hello everyone.

I am setting up a simple spreadsheet which has a few formulas in it which I need to combine into one cell. Here is an example of what I am working with:

Ref number			Date received	Target date RAG
170113-02	=LEFT(A2, 6)	=DATE(RIGHT(B2, 2)+100, MID(B2,3,2), LEFT(B2, 2))	03/05/2013	=NETWORKDAYS(C2,D2,0)

<tbody>
</tbody>

In the first column, I am presented with a reference number, which consists of a date, followed by a dash, and a further two numbers.

The second column strips out the dash and the extra numbers, so I am just left with the date portion.

The third column then converts this, so it is actually in a date format (If anyone knows a more elegant way of doing this, please let me know).

The fourth column is just a plain date, not associated with the first three columns.

The fifth column compares the dates in columns three and four, to ascertain the number of working days between the two. (In this case, the number of working days between 17/01/2013 and 03/05/2013).

Now, what I need to do, is to combine columns two, three and five, so that the calculations all occur in the same space. Does anyone know how I can do this.

Apologies if this post is unnecessarily long and rambling; let me know if you need more clarification on anything and I'll do my best.

Thanks!

 

[FormR]

Hi, welcome to the forum:

Maybe like this:

Excel Workbook
	A	B	C	D	E
1	Ref number			Date received	Target date RAG
2	170113-02			03/05/2013	77
Sheet1

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean?

Excel Workbook
	A	B	C	D	E
1	Ref number			Date received	Target date RAG
2	170113-02			3/05/13	77
Date Calc

Edit: I defer to FormR

 

[SSawyer]

That worked like a charm, thank you so much!

 

[etaf]

=networkdays(date(right(left(a2, 6),2)+100,mid(left(a2, 6),3,2),left(left(a2, 6),2)),d2,0)

 

[Peter_SSs]

=networkdays(date(right(left(a2, 6),2)+100,mid(left(a2, 6),3,2),left(left(a2, 6),2)),d2,0)

Note that using the DATE() function, as you did, you wouldn't need to keep extracting the left 6 characters before grabbing the digits you want.

=NETWORKDAYS(DATE(MID(A2,5,2)+100,MID(A2,3,2),LEFT(A2,2)),D2,0)

 

[etaf]

Thanks
Peter_SSs

I simply subsitiute the different formulas, to show that aspect , rather than simplify - should have mentioned that in the post - thanks - re-looking at the expression, it looks silly now