Help

Formula to automatically calculate the duration of a meeting in decimal

Topic Labels: Formulas
309 1
cancel
Showing results for 
Search instead for 
Did you mean: 
GregLmt
4 - Data Explorer
4 - Data Explorer

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

1 Reply 1
Sho
11 - Venus
11 - Venus

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)
)