Forum Discussion

vkandala's avatar
vkandala
Frequent Visitor
8 years ago
Solved

convert run_duration (DD: HH: MM: SS) column to Seconds.

Hello,

 

I am trying to convert run_duration (DD: HH: MM: SS)  column to Seconds. 

For example, if Run duration is 1 Day: 1 hour: 1 minute: 1 second I need to convert it to 90061 seconds

 

86400 + 3600 + 60 + 1 = 90061.

 

 

Thanks

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago
    Seconds = VAR DurationString = SUBSTITUTE(Durations1[Duration], ":", "|")
VAR Days = PATHITEM(DurationString,1)
VAR Hours = PATHITEM(DurationString,2)
VAR Minutes = PATHITEM(DurationString,3)
VAR Seconds = PATHITEM(DurationString,4)
RETURN Days * 86400 + Hours * 3600 + Minutes * 60 + Seconds

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago
    Seconds = VAR DurationString = SUBSTITUTE(Durations1[Duration], ":", "|")
VAR Days = PATHITEM(DurationString,1)
VAR Hours = PATHITEM(DurationString,2)
VAR Minutes = PATHITEM(DurationString,3)
VAR Seconds = PATHITEM(DurationString,4)
RETURN Days * 86400 + Hours * 3600 + Minutes * 60 + Seconds
    • Eric_Zhang's avatar
      Eric_Zhang
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago
      @smoupre wrote:
      Seconds = VAR DurationString = SUBSTITUTE(Durations1[Duration], ":", "|")
VAR Days = PATHITEM(DurationString,1)
VAR Hours = PATHITEM(DurationString,2)
VAR Minutes = PATHITEM(DurationString,3)
VAR Seconds = PATHITEM(DurationString,4)
RETURN Days * 86400 + Hours * 3600 + Minutes * 60 + Seconds

      Greg_Deckler

      Wow, it is really tricky to use PATHITEM to get the substrings. Awesome work :smileylol:.