Subtracting Dates

[cart0250]

Hello

I'm trying to subtract a date/time in m/d/yyyy h:mm format from the current date/time.

I have the following formula :
=IFERROR(INT(NOW()-A1)&" days " & TEXT(NOW()-A1,"h""hrs ""m""mins"""),"")

If the current date/time is 1/24/2018 8:51 and the date in A1 is 1/23/2018 1:20, the formula correctly yields the difference as 1 days 7hrs 31mins.

What I'd like is to just calculate the difference in hours and minutes (not days)

So in this example, result would be 31 hrs 31 mins.

Does anyone know how can I modify the above formula?

Thanks for your help

 

[wideboydixon]

=IFERROR(TEXT(NOW()-A1,"[hh]""hrs"" m""mins"""),"")

WBD

 

[FormR]

Hi, try like this:

=TEXT(NOW()-A1,"[h] ""hrs"" m ""Mins""")

 

[FormR]

Or alternatively just use:

=NOW()-A1

And custom format the cell that contains the formula using.

[h] "hrs" m "Mins"

 

[cart0250]

Thank you everyone. Appreciate the help.

 

[jgangone]

Is there a way to do this but just for the date and not the time? I have dates that are downloaded as X/XX/XXXX XX:XX:XX PM but I want to subtract one date field from another. My chart shows too many dates b/c of the times included.

 

[wideboydixon]

You can remove the time part of a date/time by using INT(). For example:

=INT(B1)-INT(A1)

WBD

 

[dhunton]

Is there a way to do this with just the time? What I've done is just subtract one date from the other, and formatted the cell where I was putting the info to display hh:mm:ss and that worked fine at first.

Now I need to find out if that hh:mm:ss is greater or less than 00:15:00, but I can't figure out how to set up the subtraction so that I can make that comparison. Any ideas would be appreciated.

 

[wideboydixon]

Extracting just the time part of a date time:

=A1-INT(A1)

WBD