Issues with reverting numbers to dates

[JDJong]

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 !


[TABLE="class: cms_table, width: 1025"]
<tbody>[TR]
[TD]01FI000389[/TD]
[TD="align: right"]876789[/TD]
[TD]01.01.2012-01.07.2012[/TD]
[TD][/TD]
[TD]FI (fInance department)[/TD]
[TD]Finance_Department_01.01- 01.07.2012_PN_876789[/TD]
[/TR]
[TR]
[TD]01FI000390[/TD]
[TD]A23123[/TD]
[TD]01.05.2016-01.11.2016[/TD]
[TD][/TD]
[TD]FI (fInance department)[/TD]
[TD]Finance_Department_01.05- 01.11.2016_PN_A23123[/TD]
[/TR]
[TR]
[TD]01FI000391[/TD]
[TD="align: right"]767890[/TD]
[TD]09.01.2018-09.07.2018[/TD]
[TD][/TD]
[TD]FI (fInance department)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02QC000340[/TD]
[TD="align: right"]878788[/TD]
[TD]09.01.2018-09.07.2019[/TD]
[TD][/TD]
[TD]QC (Quality control)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02QC000341[/TD]
[TD="align: right"]345434[/TD]
[TD]09.01.2018-09.07.2020[/TD]
[TD][/TD]
[TD]QC (Quality control)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02QC000342[/TD]
[TD="align: right"]876789[/TD]
[TD]09.01.2018-09.07.2021[/TD]
[TD][/TD]
[TD]QC (Quality control)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03MI000425[/TD]
[TD="align: right"]989870[/TD]
[TD]09.01.2018-09.07.2022[/TD]
[TD][/TD]
[TD="colspan: 2"]MI (Maintenance department)[/TD]
[/TR]
[TR]
[TD]03MI000426[/TD]
[TD="align: right"]678768[/TD]
[TD]09.01.2018-09.07.2023[/TD]
[TD][/TD]
[TD="colspan: 2"]MI (Maintenance department)[/TD]
[/TR]
[TR]
[TD]03MI000427[/TD]
[TD="align: right"]565456[/TD]
[TD]09.01.2018-09.07.2024[/TD]
[TD][/TD]
[TD="colspan: 2"]MI (Maintenance department)[/TD]
[/TR]
[TR]
[TD]10SR000001[/TD]
[TD="align: right"]989899[/TD]
[TD]09.01.2018-09.07.2025[/TD]
[TD][/TD]
[TD="colspan: 2"]SR (Senior Management)[/TD]
[/TR]
[TR]
[TD]10SR000002[/TD]
[TD="align: right"]898989[/TD]
[TD]09.01.2018-09.07.2026[/TD]
[TD][/TD]
[TD="colspan: 2"]SR (Senior Management)[/TD]
[/TR]
[TR]
[TD]10SR000003[/TD]
[TD="align: right"]787878[/TD]
[TD]09.01.2018-09.07.2027[/TD]
[TD][/TD]
[TD="colspan: 2"]SR (Senior Management)[/TD]
[/TR]
[TR]
[TD]11BD000333[/TD]
[TD="align: right"]888989[/TD]
[TD]09.01.2018-09.07.2028[/TD]
[TD][/TD]
[TD]BD (Board of Director)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11BD000334[/TD]
[TD="align: right"]998989[/TD]
[TD]09.01.2018-09.07.2029[/TD]
[TD][/TD]
[TD]BD (Board of Director)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11BD000335[/TD]
[TD="align: right"]656567[/TD]
[TD]09.01.2018-09.07.2030[/TD]
[TD][/TD]
[TD]BD (Board of Director)


[/TD]
[TD]

[/TD]
[/TR]
[TR]
[TD]





[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JazzSP8]

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

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

?

 

[JDJong]

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

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

?

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 ?