IFTTT: Track Your Work Hours - Upgraded

gistfile1.txt

# Upgrade to the "DO" recipe "Track Your Work Hours" by @dmerrick
# To use, connect to the recipe (https://ifttt.com/recipes/227069-track-your-work-hours) and change the "Formatted Row" content with the one below.
# It gives the time in "HHhMM" format of a work time, and the total of all work times in the same readable format.

{{OccurredAt}} ||| =IF(ISODD(ROW()), "Started", "Stopped") ||| =IF(ISEVEN(ROW()),ROUND(((DATEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4)), "\w+ \d{2}, \d{4}")) + TIMEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4)), "\d{2}:\d{2}[A|P]M$"))) - ( DATEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2,4)), "\w+ \d{2}, \d{4}")) + TIMEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2,4)), "\d{2}:\d{2}[A|P]M$")))) * 24, 2),"") ||| =if(ISEVEN(ROW()), CONCATENATE(IFERROR(CONCAT(REGEXEXTRACT(text(INDIRECT(ADDRESS(ROW(), COLUMN() -1, 4)), "###.##"), "^\d+"),"h"), ""), ROUND(60* VALUE(REGEXEXTRACT(text(INDIRECT(ADDRESS(ROW(), COLUMN() -1, 4)), "###.###"), "\.\d*"))), "mins"), "") ||| =IF(ISEVEN(ROW()), IFERROR(VALUE(IF(ROW() = 4, VALUE(INDIRECT(ADDRESS(ROW() - 2, COLUMN() - 2, 4))) + VALUE(INDIRECT(ADDRESS(ROW(), COLUMN() - 2, 4))), VALUE(INDIRECT(ADDRESS(ROW() - 2, COLUMN(), 4))) + VALUE(INDIRECT(ADDRESS(ROW(), COLUMN() - 2, 4))))), ""), "") ||| =if(AND(ISEVEN(ROW()), ROW()<>2), CONCATENATE(IFERROR(CONCAT(REGEXEXTRACT(text(INDIRECT(ADDRESS(ROW(), COLUMN() -1, 4)), "###.##"), "^\d+"),"h"), ""), ROUND(60* VALUE(REGEXEXTRACT(text(INDIRECT(ADDRESS(ROW(), COLUMN() -1, 4)), "###.###"), "\.\d*"))), "mins"), "")

Hi!
It seems to have a problem with these formulas.

I solve the first problem =IF(ISODD(ROW()), "Started", "Stopped")
to
=IF(ISODD(ROW()); "Started"; "Stopped")

But I can't find the problem for the thrid column... :-( Can U help me?!

Thanks you very much!

Best,
Jonathane

so if i use this recipe

https://ifttt.com/applets/NAMgXbLj

it's if i press ifttt button start recording working huors

this recipe write the date in these format

February 02, 2019 at 07:32AM

it's fine nothing wrong in it

but if i choose http or webhook or any thing else as trigger to do google sheets it write the date in these format

February 2, 2019 at 07:32AM

the difference

February 02, 2019 at 07:32AM

the difference in the ( 0 ) that's before the day of the month

which are big problem because if the ( 0 ) is not there it's an error , only happen in the beginning of the month from day 1 to 9 only and i dot want to manually add the ( 0 ) for each employees i have for the first 9 days

and i use it to traking sleeping time and other stuff

the formula is

 {{OccurredAt}} ||| =IF(ISODD(ROW()), "Started", "Stopped") |||=IF(ISEVEN(ROW()),ROUND(((DATEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4)),"\w+ \d{2}, \d{4}")) + TIMEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4)), "\d{2}:\d{2}[A|P]M$"))) - ( DATEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2,4)), "\w+ \d{2}, \d{4}")) + TIMEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2,4)), "\d{2}:\d{2}[A|P]M$")))) * 24, 2),"")|||

so if the 0 is there every thing is fine but if it's not there i receive an error #N/A

is the problem how the recipe record date or how ifttt recorded and how i can fix it

please help

one more thing can i do if the working hours less than 8 hours then send or trigger ifttt