Converting time

Capps

New Member
Joined
Aug 14, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hi,

I've been trying to figure this out but haven't found a solution yet.

What I want as an example is
0.00:04:53

What I'm getting when the table loads from power
0.003391204

The code section looks like

#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns2",{{"* FIRST RELEASE DATE [dd/mm/yyyy]:", type date}, {"* AVAILABLE DATE [dd/mm/yyyy]:#(lf) must be same or later than release date", type date}, {"DURATION VIDEO", type duration}}),
 

Attachments

  • 1597801198526.png
    1597801198526.png
    3.9 KB · Views: 3

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
maybe
Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type duration}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"Column1", each Text.BeforeDelimiter(Text.From(_, "en-GB"), ".", {0, RelativePosition.FromEnd}), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Column1", type duration}})
in
    #"Changed Type1"
Column1Column1
0.0033912040.00:04:53
 

Capps

New Member
Joined
Aug 14, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hi, thank you for your response. This could be my lack of understanding.

I believe what you've suggested works but it's essentially what is happening.

I can already see the final "0.00:04:53" but when I load the table to excel it automatically converts to "0.003391204". How do I keep it as "0.00:04:53"?
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
you need define type of data in last step as duration
as you can see I set data type as duration twice, so try the same
dur.png

or/and in worksheet use Custom format: d.hh:mm:ss
and set Properties
format.png
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

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
Top