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
Page 1 / 1
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)
)
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.