Convert SAP data field into Excel Date and Time

rstuart19

New Member
Joined
Jul 20, 2019
Messages
10
Need to convert following cell data by either formula or clever format number custom style

CURRENT DATA IMPORTED

STARTFINISHhh/mm
Mon Jan 08 02:20:13 AEST 2018Mon Mar 08 09:40:17 AEST 2018??
imported as text, cant format number to date/timeimported as text, cant format number to date/time<strike></strike><strike></strike>

<tbody>
</tbody>

<tbody>
</tbody>

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)

STARTFINISHhh/mm
Mon Jan 08 2008 02:20:13Mon Mar 08 2008 09:40:173:00:00
above becomes excel date/time formatabove becomes excel date/time format<strike></strike>Can be calculated

<tbody>
</tbody>

<tbody>
</tbody>

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?
 

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.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,824
why result year is 10 years back and how you calculated hh/mm result?!

anyway maybe this:

STARTFINISH
Mon Jan 08 02:20:13 AEST 2018Mon Mar 08 09:40:17 AEST 2018
StartEndDuration
08/01/2018 02:20​
08/03/2018 09:40​
28.07:20:04​
where Duration has format: d.hh:mm:ss

and M-code:
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]
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,824
a little correction:

StartEndDuration
08/01/2018 02:20​
08/03/2018 09:40​
1423:20:04​
duration format is: [h]:mm:ss

Code:
[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

New Member
Joined
Jul 20, 2019
Messages
10
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,824
do you know this is NOT vba but M-code for PowerQuery aka Get&Transform?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,041
Office Version
2010
Platform
Windows
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

New Member
Joined
Jul 20, 2019
Messages
10
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

New Member
Joined
Jul 20, 2019
Messages
10
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

New Member
Joined
Jul 20, 2019
Messages
10
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,473
Messages
5,487,088
Members
407,577
Latest member
afonsomira

This Week's Hot Topics

Top