Net duration between 2 dates AND hours
Answers


Hi @Paul Newcome ,
Do you have any ideas why, in the DateOnly formula, the result is the next day if finish time is after 20h? See below

It looks like you may have run into the annoying timezone issue. Can you post the exact formula you are currently using? We should be able to tweak it to basically say "if the hour is greater then or equal to 20 then subtract 1 day".
thinkspi.com

Hi @Paul Newcome,
You were right problem was some people were not using the same timezone. It's fixed now :)


Problem seems finally not resolved. Please take a look at my picture below : task ended at 20:12 on December 5th but end date, with formula DATE ONLY, is December 6th.
Any idea? Thanks :)

Right. It looks like anything after 2000 is pushing to the next day. You would need to add an IF statement that says if the hour is greater than or equal to 20, subtract one day.
=DATEONLY([Date Column]@row)  IF(VALUE(MID([Date Column]@row, 10, 2)) >= 20, 1, 0)
thinkspi.com

@Paul Newcome Error message : Unparseable, date column format

Make sure you updated the column names to the correct names that are in your sheet, and you may need to swap the commas out for semicolons.
thinkspi.com
Help Article Resources
Categories
Check out the Formula Handbook template!