Convert SAP data field into Excel Date and Time
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Convert SAP data field into Excel Date and Time

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Convert SAP data field into Excel Date and Time

    Need to convert following cell data by either formula or clever format number custom style

    CURRENT DATA IMPORTED

    START FINISH hh/mm
    Mon Jan 08 02:20:13 AEST 2018 Mon Mar 08 09:40:17 AEST 2018 ??
    imported as text, cant format number to date/time imported as text, cant format number to date/time

    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)

    START FINISH hh/mm
    Mon Jan 08 2008 02:20:13 Mon Mar 08 2008 09:40:17 3:00:00
    above becomes excel date/time format above becomes excel date/time format Can be calculated

    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?

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,560
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Convert SAP data field into Excel Date and Time

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

    anyway maybe this:

    START FINISH
    Mon Jan 08 02:20:13 AEST 2018 Mon Mar 08 09:40:17 AEST 2018
    Start End Duration
    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:
    // 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
    Last edited by sandy666; Jul 21st, 2019 at 04:13 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,560
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Convert SAP data field into Excel Date and Time

    a little correction:

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

    Code:
    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
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert SAP data field into Excel Date and Time

    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?

  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,560
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Convert SAP data field into Excel Date and Time

    do you know this is NOT vba but M-code for PowerQuery aka Get&Transform?
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert SAP data field into Excel Date and Time

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

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,953
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Convert SAP data field into Excel Date and Time

    Quote Originally Posted by rstuart19 View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert SAP data field into Excel Date and Time

    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 👍🏻

  9. #9
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert SAP data field into Excel Date and Time

    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.

  10. #10
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert SAP data field into Excel Date and Time

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •