Automatic duration calculator

[GregLmt]

Hi guys,
I have a column with the time sheets of a meeting, for instance "9am-12am// 1pm-4pm" or in european terms "9h-12h//13h-16h". I would like to find a formula to transform those numbeers into decimal hours in a new column. In this case, it would give 6. 

Is it possible with a formula ? Or by any other mean ? 

Thanks a lot,
Greg

[Sho]

Hi @GregLmt ,

I have tried to come up with a formula for the following sample.

10am-12am// 2pm-5pm
9am-12am//1pm-4pm
9am-11am//12am-3pm
9am-1pm

REGEX_EXTRACT({durationText},"^\\d{1,2}\\w{2}-(\\d{1,2})")-
REGEX_EXTRACT({durationText},"^(\\d{1,2})\\w{2}-")+
IF(REGEX_MATCH({durationText},"^\\d{1,2}am-\\d{1,2}pm.*"),12)+
IF(FIND("//",{durationText}),
  REGEX_EXTRACT({durationText},"//\\s?\\d{1,2}\\w{2}-(\\d{1,2})\\w{2}")-
  REGEX_EXTRACT({durationText},"//\\s?(\\d{1,2})\\w{2}-")+
  IF(REGEX_MATCH({durationText},"^.*//\\s?\\d{1,2}am-\\d{1,2}pm"),12)
)