Convert SAP data field into Excel Date and Time

R

rstuart19

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

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
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:
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:
Upvote 0
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

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]
 
Upvote 0
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?
 
Upvote 0
rstuart19 said:
Hi thanks for helping. IT was a copy and paste error - I was trying to show same date and year, just 2 hour difference.
Click to expand...
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.
 
Upvote 0
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 ??
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Convert Date Problem
Replies
4
Views
731
Michele317
M
A
  • Question Question
Add a specified number of working hours to a date & time, given working day start, end and break times and holidays
Replies
2
Views
828
AquaBuoy
A
T
  • Question Question
Open CSV with VBA, convert to ANSI and a cell "75 %" is changed to format type percent and the value is changed to "0.75"
Replies
1
Views
305
Tanquen
T
T
  • Solved
Problem with date format when reading from workbook via SQL / ADODB
Replies
7
Views
745
Thebatfink
T
C
  • Question Question
ODD DATE & TIME (FORMAT) CALCULATION
Replies
8
Views
1K
Tetra201
Tetra201

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top