Struggling with a Formula that has to do with date and time

[Tclinken]

I need a formula that has a cell which counts dwelling time in hours between: Cell A2 [=NOW() function] and [mmm dd yyyy H:mm) date format]

Example:
How long a package has been on a front door step,
in A1 [this is the date and time the package arrived], A2 [Is the =NOW() function]
I want A3 to display difference in time in hours between A2 and A1,

 

[Joe4]

Are you saying that your formula:
=ISNUMBER(A1)
is returning FALSE?

If so, that means that your entry in A1 is NOT a valid date entry, and is rather a Text entry (which you cannot use in mathematical calculations).
How is this value being populated?

 

[Tclinken]

A1 is pulling the date and time from another sheet using =Vlookup

 

[Joe4]

OK, that sheet and column that the VLOOKUP is pulling from, how are those date/times populated?
It sounds like those are Text, and that is your problem.

 

[Joe4]

If you are unable to fix the Date/Time entries to make them truly Date/Time entries and not text, the following formula should work as long as your data format is consistent.

=A2-DATEVALUE(LEFT(SUBSTITUTE(A1," 201",", 201"),LEN(A1)-5))-TIMEVALUE(RIGHT(A1,5))

Not the prettiest thing, but it should work.

 

[steve the fish]

I think these work different ways in different locales but this is how i would go. Im in UK by the way:

=A2-SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,4),"")," "," "&LEFT(A1,4),1)

 

[Tclinken]

will try this formulas

but the vlookup is pulling from a sheet that is pulling info directly from the web and spits the data back onto the sheet in this way EX: Feb 19 2017 16:41.

 

[Tclinken]

Thankyou that absouletly worked!
=A2-DATEVALUE(LEFT(SUBSTITUTE(A1," 201",", 201"),LEN(A1)-5))-TIMEVALUE(RIGHT(A1,5))this did

 

[Joe4]

You are welcome.

Steve's reply worked for me too, and is a little shorter than mine.

 

[steve the fish]

You are welcome.

Steve's reply worked for me too, and is a little shorter than mine.

That surprises me because your solution didnt work on my machine which is why i posted the alternative.

 

[Tclinken]

Okay just realized a problem im having with the both the formulas now, Its doing everything its supposed to do; but the problem is it doesnt report time correctly after 24 hours it just resets back to zero and starts counting again i just need it to do a dwelling time even if its infinite, just to continuously count time in hrs from the arrival time.