Issues with reverting numbers to dates

J

JDJong

New Member
Joined
May 2, 2018
Messages
39
Hi guys,

See below, I have used the following formula intending to retrieve the red bits.

=LOOKUP(MID(A2,3,2),$J$1:$K$5)&LEFT(C2,5)&"-"&D2&"_"&"PN_"&B2

instead, it Returns as:

Finance_Department_43107-43287_PN_876789

I tried to Change the Format cells as Dates, it still won't work.

oddly enough, I have paste the data on a new sheet, it came out as date Format.

Could anybody advise with probable causes and Solutions?

appreciate the help !


01FI00038987678901.01.2012-01.07.2012FI (fInance department)Finance_Department_01.01- 01.07.2012_PN_876789
01FI000390A2312301.05.2016-01.11.2016FI (fInance department)Finance_Department_01.05- 01.11.2016_PN_A23123
01FI00039176789009.01.2018-09.07.2018FI (fInance department)
02QC00034087878809.01.2018-09.07.2019QC (Quality control)
02QC00034134543409.01.2018-09.07.2020QC (Quality control)
02QC00034287678909.01.2018-09.07.2021QC (Quality control)
03MI00042598987009.01.2018-09.07.2022MI (Maintenance department)
03MI00042667876809.01.2018-09.07.2023MI (Maintenance department)
03MI00042756545609.01.2018-09.07.2024MI (Maintenance department)
10SR00000198989909.01.2018-09.07.2025SR (Senior Management)
10SR00000289898909.01.2018-09.07.2026SR (Senior Management)
10SR00000378787809.01.2018-09.07.2027SR (Senior Management)
11BD00033388898909.01.2018-09.07.2028BD (Board of Director)
11BD00033499898909.01.2018-09.07.2029BD (Board of Director)
11BD00033565656709.01.2018-09.07.2030BD (Board of Director)











<tbody>
</tbody>
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Untested because you haven't included Cols J & K so can't see what data is there, but something along the lines of...

Code: 
=LOOKUP(MID(A2,3,2),$J$1:$K$5)&FORMAT(LEFT(C2,5),"DD.MM")&"-"&FORMAT(D2,"DD.MM.YYYY")&"_"&"PN_"&B2

?
 
Upvote 0
JazzSP8 said:
Untested because you haven't included Cols J & K so can't see what data is there, but something along the lines of...

Code: 
=LOOKUP(MID(A2,3,2),$J$1:$K$5)&FORMAT(LEFT(C2,5),"DD.MM")&"-"&FORMAT(D2,"DD.MM.YYYY")&"_"&"PN_"&B2

?
Click to expand...

Thanks for your reply. Cols J and K are:

FI Finance_department_
QC Quality_Control_
MI Maintenance_department_
SR Senior_management_
BD Board_of_director_

I tried your formula, and it Returns as #NAME ? :eek::confused:
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Need help with formula. - perhaps wildcard and a mix of indirect?
2
Replies
10
Views
2K
JDJong
J

Forum statistics

Threads
1,214,806
Messages
6,121,667
Members
449,045
Latest member
Marcus05

Share this page

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
Back
Top