Net duration between 2 dates AND hours

Paul Newcome · 12/01/21

Happy to help. 👍️

Julie Barbeau · 12/02/21

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

Paul Newcome · 12/02/21

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".

Julie Barbeau · 12/02/21

Hi @Paul Newcome,

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

Paul Newcome · 12/03/21

Glad you were able to get it sorted.

Julie Barbeau · 12/06/21

@Paul Newcome

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

Paul Newcome · 12/06/21

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)

Julie Barbeau · 12/06/21

@Paul Newcome Error message : Unparseable, date column format

Paul Newcome · 12/06/21

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 semi-colons.

Explore. Ask. Answer.

Net duration between 2 dates AND hours

Answers