Net duration between 2 dates AND hours

2»

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

  • 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



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Glad you were able to get it sorted.

    thinkspi.com

  • @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
    Paul Newcome ✭✭✭✭✭✭

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.

    thinkspi.com

Help Article Resources