How to calculate date and time duration into Days in google sheet?-CodePudding

I know it's basic but I'm new to this. I just want to know how can I calculate the days duration of the two dates?

For example, I have my start date and time 11/22/2021 15:20:43 and end date and time 11/23/2021 14:51:29 I want to calculate the total days from start to end date and time. Also, If start date time column is BLANK, return the value to blank.

Thank you

CodePudding user response：

Assuming start dates in column A and end dates in column B, you can try

={"Duration in Days"; Arrayformula(if(len(A2:A) * len(B2:B), datedif(A2:A, B2:B, "d"),))}

Change ranges to suit and see if that works?

EXAMPLE

REFERENCES:

update:
```
=INDEX(IFERROR(1/(1/DAYS(
 REGEXREPLACE(TO_TEXT(B1:B), "(.|..)[\/\-\.](.|..)[\/\-\.](. ) (.*$)", "$2\/$1\/$3"), 
 REGEXREPLACE(TO_TEXT(A1:A), "(.|..)[\/\-\.](.|..)[\/\-\.](. ) (.*$)", "$2\/$1\/$3")))))
```
demo sheet

CodePudding user response：

All you need to do is use the following formula: '=(C2-A2)'. This will give you the elapsed time between the two cells and display it as hours. You can take this calculation further by adding dates too. This is useful if you have work shifts that go more than 24 hours or that include two days within a single shift.

update:

demo sheet