I have searched a bit and can't figure this out. I've tried using Now() in the formula along with putting it in a cell and running the formula against it.

I have a column of date/times. I would like to take the current date/time and subtract this column to output the number of hours difference.

I have tried a few things including simply =I1-G1. I get function MINUS parameter 2 expect number values. But 'Last Played' is text and cannot be coerced to a number.

It would be ideal to somehow use Now() in the formula to eliminate the extra column. I manually entered the dates in "Last Played" then converted to date/time.

Any ideas?

  • 1
    Adding some sample data to your question would be useful to illustrate what you're trying to do. – ale Oct 7 '16 at 17:37
  • give us an example of "Last Played": "2016-07-04 11:23:45 (-0400)" is a different level of complexity from "Monday July 4th 2016 @ 11:23:45 AM EDT" – mhoran_psprep Oct 8 '16 at 11:10
  • @justchil: Welcome to Web Applications. Please take the tour and checkout How to Ask. – Rubén Oct 8 '16 at 23:19

Short answer

If your column of date/time are date/time values and it is Column A try =NOW()-A1


If something like the =NOW()-A1 doesn't work for you it's very likely that the values of the column of date/times are being interpreted by Google Sheets as strings instead of dates/times.

Depending on the format, you could use DATEVALUE built-in function or should use a complex formula or custom function to parse each date/time component and return a date value.

The following is a demostration using NOW:

Account main language setting: Spanish
Spreadsheet locale setting: Mexico


A1: =today()+5/25+20/24/60
B1: =now()-A1, formatted as duration.


 |                    A                     |     B      |
1|   sábado, 8 de octubre de 2016, 05:08:00 |   13:14:361|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.