Skip to main content

Formula to automatically calculate the duration of a meeting in decimal

  • August 31, 2023
  • 1 reply
  • 13 views
G
Forum|alt.badge.img+1

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

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • 560 replies
  • August 31, 2023

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

 


Terms & ConditionsAccessibility statement