Combining formulas into a single cell

SSawyer

New Member
Joined
May 9, 2013
Messages
2
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 receivedTarget 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!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
Hi, welcome to the forum:

Maybe like this:
Excel Workbook
ABCDE
1Ref numberDate receivedTarget date RAG
2170113-0203/05/201377
Sheet1
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Is this what you mean?

Excel Workbook
ABCDE
1Ref numberDate receivedTarget date RAG
2170113-023/05/1377
Date Calc



Edit: I defer to FormR :)
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,413
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
=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

Well-known Member
Joined
Oct 24, 2012
Messages
4,413
Office Version
  1. 365
Platform
  1. MacOS
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 :(
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top