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?

 

[sandy666]

why result year is 10 years back and how you calculated hh/mm result?!

anyway maybe this:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]START[/td][td=bgcolor:#5B9BD5]FINISH[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mon Jan 08 02:20:13 AEST 2018[/td][td=bgcolor:#DDEBF7]Mon Mar 08 09:40:17 AEST 2018[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Start[/td][td=bgcolor:#70AD47]End[/td][td=bgcolor:#70AD47]Duration[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

08/01/2018 02:20​

[/td][td=bgcolor:#E2EFDA]

08/03/2018 09:40​

[/td][td=bgcolor:#E2EFDA]

28.07:20:04​

[/td][/tr]
[/table]where Duration has format: d.hh:mm:ss

and M-code:

[SIZE=1]// Table5
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    RPL = Table.ReplaceValue(Source," AEST","",Replacer.ReplaceText,{"START", "FINISH"}),
    Time1 = Table.AddColumn(RPL, "Text Range", each Text.Middle([START], 11, 8), type text),
    Time2 = Table.AddColumn(Time1, "Text Range.1", each Text.Middle([FINISH], 11, 8), type text),
    TBD1 = Table.AddColumn(Time2, "Text Between Delimiters", each Text.BetweenDelimiters([START], " ", " ", 0, 1), type text),
    TAD1 = Table.AddColumn(TBD1, "Text After Delimiter", each Text.AfterDelimiter([START], " ", {0, RelativePosition.FromEnd}), type text),
    TBD2 = Table.AddColumn(TAD1, "Text Between Delimiters.1", each Text.BetweenDelimiters([FINISH], " ", " ", 0, 1), type text),
    TAD2 = Table.AddColumn(TBD2, "Text After Delimiter.1", each Text.AfterDelimiter([FINISH], " ", {0, RelativePosition.FromEnd}), type text),
    Merge1 = Table.CombineColumns(TAD2,{"Text Between Delimiters", "Text After Delimiter", "Text Range"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    Merge2 = Table.CombineColumns(Merge1,{"Text Between Delimiters.1", "Text After Delimiter.1", "Text Range.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged.1"),
    ROC = Table.SelectColumns(Merge2,{"Merged", "Merged.1"}),
    Type = Table.TransformColumnTypes(ROC,{{"Merged", type datetime}, {"Merged.1", type datetime}}),
    Subtract = Table.AddColumn(Type, "Subtraction", each [Merged.1] - [Merged], type duration),
    Rename = Table.RenameColumns(Subtract,{{"Merged", "Start"}, {"Merged.1", "End"}, {"Subtraction", "Duration"}})
in
    Rename[/SIZE]

 

[sandy666]

a little correction:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Start[/td][td=bgcolor:#70AD47]End[/td][td=bgcolor:#70AD47]Duration[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

08/01/2018 02:20​

[/td][td=bgcolor:#E2EFDA]

08/03/2018 09:40​

[/td][td=bgcolor:#E2EFDA]

1423:20:04​

[/td][/tr]
[/table]duration format is: [h]:mm:ss

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Split = Table.SplitColumn(Source, "START", Splitter.SplitTextByAnyDelimiter({" "}, QuoteStyle.Csv)),
    Merge1 = Table.CombineColumns(Table.TransformColumnTypes(Split, {{"START.3", type text}, {"START.6", type text}, {"START.4", type text}}, "en-GB"),{"START.3", "START.2", "START.6", "START.4"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    ROC1 = Table.SelectColumns(Merge1,{"Merged", "FINISH"}),
    Split2 = Table.SplitColumn(ROC1, "FINISH", Splitter.SplitTextByAnyDelimiter({" "}, QuoteStyle.Csv)),
    Merge2 = Table.CombineColumns(Table.TransformColumnTypes(Split2, {{"FINISH.3", type text}, {"FINISH.6", type text}, {"FINISH.4", type text}}, "en-GB"),{"FINISH.3", "FINISH.2", "FINISH.6", "FINISH.4"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged.1"),
    ROC2 = Table.SelectColumns(Merge2,{"Merged", "Merged.1"}),
    Type = Table.TransformColumnTypes(ROC2,{{"Merged", type datetime}, {"Merged.1", type datetime}}),
    Duration = Table.AddColumn(Type, "Subtraction", each [Merged.1] - [Merged], type duration),
    Rename = Table.RenameColumns(Duration,{{"Merged", "Start"}, {"Merged.1", "End"}, {"Subtraction", "Duration"}}),
    TypeDec = Table.TransformColumnTypes(Rename,{{"Duration", type number}})
in
    TypeDec[/SIZE]

 

[rstuart19]

Hi thanks for helping. IT was a copy and paste error - I was trying to show same date and year, just 2 hour difference.

I am not familiar with code - are you able to put into an actual workbook and attach so i can see it work?

 

[sandy666]

do you know this is NOT vba but M-code for PowerQuery aka Get&Transform?

 

[rstuart19]

I do now! I’ll look into it again thanks for assistance.

 

[Rick Rothstein]

Hi thanks for helping. IT was a copy and paste error - I was trying to show same date and year, just 2 hour difference.

If you are looking for the time difference between cells A2 and B2, you can use this formula without converting your original "time" values to real time values...

=REPLACE(MID(B2,5,15),7,0,", "&RIGHT(B2,4))-REPLACE(MID(A2,5,15),7,0,", "&RIGHT(A2,4))

You can format that value with whatever time format you want, but if you wanted hh:mm like your post seems to show, use this as your custom format...

[hh]:mm

in case the time difference exceeds 24 hours.

 

[rstuart19]

Thanks Rick! Provided my colleague doesn’t need the start and finish for anything else other than working out time difference that’ll be nice and simple ??

 

[rstuart19]

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

So I broke it all down individually for each step and came up with this combined formula (probably way too long version of what I want to achieve) which is initially how many hh:mm:ss between the two text dates from SAP that are just text.

Where cell C2 = Finish date and time

(as text not able to simply be formatted to date and time from menu)​

Where cell B2 = Start date and time (as text not able to simply be formatted to date and time from menu)

In cell D2 = this formula to make new START date Excel format so I can subtract it from start date in cell f2

=DATE(RIGHT(B2,4),IF(MID(B2,5,3)="jan",1,IF(MID(B2,5,3)="feb",2,IF(MID(B2,5,3)="mar",3,IF(MID(B2,5,3)="apr",4,IF(MID(B2,5,3)="may",5,IF(MID(B2,5,3)="jun",6,IF(MID(B2,5,3)="jul",7,IF(MID(B2,5,3)="aug",8,IF(MID(B2,5,3)="sep",9,IF(MID(B2,5,3)="oct",10,IF(MID(B2,5,3)="nov",11,IF(MID(B2,5,3)="dec",12)))))))))))),DAY(MID(B2,9,2))))+TIME(MID(B2,12,2),MID(B2,15,2),MID(B2,18,2))

In cell E2 = this formula to make new FINISH date Excel format so I can subtract it from start date in cell f2

=DATE(RIGHT(C2,4),IF(MID(C2,5,3)="jan",1,IF(MID(C2,5,3)="feb",2,IF(MID(C2,5,3)="mar",3,IF(MID(C2,5,3)="apr",4,IF(MID(C2,5,3)="may",5,IF(MID(C2,5,3)="jun",6,IF(MID(C2,5,3)="jul",7,IF(MID(C2,5,3)="aug",8,IF(MID(C2,5,3)="sep",9,IF(MID(C2,5,3)="oct",10,IF(MID(C2,5,3)="nov",11,IF(MID(C2,5,3)="dec",12)))))))))))),DAY(MID(C2,9,2)))+TIME(MID(C2,12,2),MID(C2,15,2),MID(C2,18,2))​

In cell F2 - to calculate HH:MM:SS difference (number formatted)

=E2-D2

However I'm sure one of you has a compact version of what I did above to get Excel date and time format. But I figured it out eventually.

 

[rstuart19]

Ignore all that LOL can't subtract the date if I change it - only subtracts the time so won't add 24 hrs to the time of day if i change start day to 1 day earlier. I'll just learn the m-code and power query stuff