Search code examples
looker-studio

How to Calculate Duration in Hours from PM to AM Using Google Data Studio

I'm trying to get the duration in hours using data stored in Google sheets using the following fields and display the data in Google Data Studio.

enter image description here

I was able to calculate them correctly in google sheets. However, I want google studio to have them calculated. I tried

((CAST(REGEXP_EXTRACT(End Time,"^(\\d+):")AS NUMBER)*60*60) + (CAST(REGEXP_EXTRACT(End Time,"^\\d+:(\\d+)")AS NUMBER)*60) + NARY_MAX(CAST(REGEXP_REPLACE(End Time,".*(PM)$","43200")AS NUMBER),0)) -

((CAST(REGEXP_EXTRACT(Start Time,"^(\d+):")AS NUMBER)6060) + (CAST(REGEXP_EXTRACT(Start Time,"^\d+:(\d+)")AS NUMBER)60) + NARY_MAX(CAST(REGEXP_REPLACE(Start Time,".(PM)$","43200")AS NUMBER),0))

This works for Start time and End Time being on the same day. But how can this be transformed if the end time is on the following day? Thanks!

Solution

  • I had fixed this by creating a parallel table then cast that field as number, then changed the data type into Duration (sec.)