Formula for calculating elapsed time between two cells that have full date and time?

blemon · Jun 12, 2021

Hello,
I tried to search the forum for this issue but I wasn't able to find anything for this specific issue (unless I was searching the wrong term). Trying to track late arrivals for appointments.

I have two cells:

And in the third column, I'd like elapsed time. I get the #VALUE! error for any formula I try, (have tried INT, DATETIME, and even CONCACTENATE to see if I could split the cells up and others I can't remember at the moment), and I've also tried different formats of the cells to see if it's just a format issue. Nothing works.
Also, does the fact that I am pulling the data from another sheet have any effect on this?

Can anyone help me, pls?

etaf · Jun 12, 2021

are the cells formatted as DATE/TIME or are they text
if you change the format to General - do you just get a number returned
IF not - then they are text format and need to be changed to date/time
You could use DATA > Text to Columns , often that will change a text date time to real dates
otherwise
=DATEVALUE(LEFT(B2,10))+TIMEVALUE(RIGHT(B2,8))
to change the value in B2 to a real date

Toadstool · Jun 12, 2021

Hi Blemon,

The dates are left aligned which suggests they're text. To convert to a date format use DATEVALUE and TEXTVALUE. I'm assuming you want elaped in hours and minutes so remember if the elapsed time exceeds 24 hours you'll need to Format, Custom [h]:mm

If the time is negative you will see hash signs.

Blemon.xlsx

B

C

D

1

Appt Time

Time In

Difference

2

3-Jun-21 04:30 PM

4-Jun-21 03:25 PM

22:55

3

4-Jun-21 03:25 PM

4-Jun-21 05:14 PM

01:49

4

4-Jun-21 03:25 PM

10-Jun-21 05:14 PM

145:49

5

4-Jun-21 03:25 PM

4-Jun-21 03:24 PM

################

6

4-Jun-21 03:25 PM

5-Jun-21 03:25 PM

24:00

Sheet1 (2)

Cell Formulas
Range	Formula
D2:D6	D2	=(DATEVALUE(C2)+TIMEVALUE(C2))-(DATEVALUE(B2)+TIMEVALUE(B2))

blemon · Jun 12, 2021

etaf said:
are the cells formatted as DATE/TIME or are they text
if you change the format to General - do you just get a number returned
IF not - then they are text format and need to be changed to date/time
You could use DATA > Text to Columns , often that will change a text date time to real dates
otherwise
=DATEVALUE(LEFT(B2,10))+TIMEVALUE(RIGHT(B2,8))
to change the value in B2 to a real date

thank you!

blemon · Jun 12, 2021

Toadstool said:
Hi Blemon,

The dates are left aligned which suggests they're text. To convert to a date format use DATEVALUE and TEXTVALUE. I'm assuming you want elaped in hours and minutes so remember if the elapsed time exceeds 24 hours you'll need to Format, Custom [h]:mm

If the time is negative you will see hash signs.

Blemon.xlsx
B C D
1 Appt Time Time In Difference
2 3-Jun-21 04:30 PM 4-Jun-21 03:25 PM 22:55
3 4-Jun-21 03:25 PM 4-Jun-21 05:14 PM 01:49
4 4-Jun-21 03:25 PM 10-Jun-21 05:14 PM 145:49
5 4-Jun-21 03:25 PM 4-Jun-21 03:24 PM ################
6 4-Jun-21 03:25 PM 5-Jun-21 03:25 PM 24:00
Sheet1 (2)
Cell Formulas
Range Formula
D2:D6 D2 =(DATEVALUE(C2)+TIMEVALUE(C2))-(DATEVALUE(B2)+TIMEVALUE(B2))

thank you!!

Tetra201 · Jun 12, 2021

Actually, if you use just

Excel Formula:

=C2-B2

you will get the desired result even though the dates/times are entered as text.

blemon · Jun 12, 2021

Tetra201 said:
Actually, if you use just

Excel Formula:

=C2-B2

you will get the desired result even though the dates/times are entered as text.

that was the first thing I tried, and it gave the error I described.

Toadstool · Jun 12, 2021

Hi Blemon,

You messaged me you were still getting an error and emailed me the sheet but it works OK. I just copied the formula down and added the same formula in column E with it formatted as Custom [hh]:mm in case the elapsed if ever greater than 24 hours.

The only thing I can think is that your Region does not recognise the date and time format of the text. What Region do you have and do you normally see am and pm on times?

Late shipment tracker.xlsx

B

C

D

E

1

2