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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,715
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,715
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,715
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,022
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,101,748
Messages
5,482,630
Members
407,355
Latest member
Beinleif

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top