Convert SAP data field into Excel Date and Time

[rstuart19]

[TABLE="width: 206"]
<tbody>[TR]
[TD]Need to convert following cell data by either formula or clever format number custom style

CURRENT DATA IMPORTED

[TABLE="width: 500"]
<tbody>[TR]
[TD]START[/TD]
[TD]FINISH[/TD]
[TD]hh/mm[/TD]
[/TR]
[TR]
[TD]Mon Jan 08 02:20:13 AEST 2018[/TD]
[TD]Mon Mar 08 09:40:17 AEST 2018[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD]imported as text, cant format number to date/time[/TD]
[TD]imported as text, cant format number to date/time<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Is it possible to convert the existing values under START/FINISH to Excel dates from which I calculate the time difference?

DESIRED DATA CONVERTED (or similar date/time)

[TABLE="width: 206"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]START[/TD]
[TD]FINISH[/TD]
[TD]hh/mm[/TD]
[/TR]
[TR]
[TD]Mon Jan 08 2008 02:20:13[/TD]
[TD]Mon Mar 08 2008 09:40:17[/TD]
[TD]3:00:00[/TD]
[/TR]
[TR]
[TD]above becomes excel date/time format[/TD]
[TD]above becomes excel date/time format<strike></strike>[/TD]
[TD]Can be calculated[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

OR do I need to use a formula to extract. I can use long forms of MID and RIGHT etc. to fashion what I need but is there a quicker way?

 

[Fazza]

what about
=MID(A2,9,2)&"/"&MID(A2,5,3)&"/"&RIGHT(A2,4)&" "&MID(A2,12,8)

 

[rstuart19]

No its ok - hadn't formatted time difference cell properly - it was HH:MM:SS not [HH]:MM:SS all good it works

 

[Rick Rothstein]

It wouldn't let me calculate the difference using your formula came up as name error.

A name error means one of the function names is spelled incorrectly or does not exist in your version of Excel. The functions I used have all been in Excel for many versions which leaves us with a misspelled function name. Did you copy/paste my formula or retype it manually? If you retyped it manually, look for a misspelling. If you copy/pasted it, did you put full cell references (column letter plus row number) when you changed the cell A2 and B2 cell references I used? I ask because a common error that could produce a name error is to put in the column letter without the row number... to Excel, that free-floating letter would look like an attempt to type a function name which, since there is no such function, a name error would result. Just so you know, I tested my formula before posting it and it worked perfectly fine for me.

 

[rstuart19]

Thanks Fazza - yes that worked with a copy and paste (and point to correct cell ref) it is a much quicker and shorter formula than my effort! Only way to learn is give it a go

I used it as follows (in case we don't need to actually change the date cells and just want the total HH:MM):

=(MID(E13,9,2)&"/"&MID(E13,5,3)&"/"&RIGHT(E13,4)&" "&MID(E13,12,8))-(MID(D13,9,2)&"/"&MID(D13,5,3)&"/"&RIGHT(D13,4)&" "&MID(D13,12,8))

 

[rstuart19]

Sorry Rick i copied straight from yours and pointed to correct cell references and it still comes up with that error - it's obviously me but Fazza's seemed to do the trick without me breaking it so all good! Thanks again.

 

[Rick Rothstein]

Sorry Rick i copied straight from yours and pointed to correct cell references and it still comes up with that error - it's obviously me but Fazza's seemed to do the trick without me breaking it so all good! Thanks again.

Just checking to be sure... the error you get is #NAME?, correct? Since you use MID and RIGHT in your formulas, and you say you used complete cell references when you changed the A2 and B2 cell references in my formula, then the only place a #NAME? error could be generated from is my use of the REPLACE function. That function has been in Excel for quite a while now, so if that is generating #NAME? error, it must be because it does not exist in your version of Excel. The only way I can think that is possible is if you have a "foreign" language version of Excel. If I am right, then you have the REPLACE function, it is just called by a different name in your version of Excel. What is your regional (locale) setting for your version of Excel?

 

[rstuart19]

Australia ?? Excel 2016

 

[Rick Rothstein]

Australia &#55356;&#56806;&#55356;&#56826; Excel 2016

The best I can tell, your version of Excel should have the REPLACE function in it. Will you do a quick test for me? Put this formula in a blank cell and tell me what is displayed in the cell...

=REPLACE("one",2,1,"X")