Subtracting Date/Time from Date/Time

[fishin]

Cell J226 is formatted as Date/Time. I want to subtract today’s current Date/Time from the Date/Time in J226 and have the result expressed as part of a day (24 hrs.) In other words, if the difference between the Date/Time in J226 minus the current/Date/Time is equal to 36 hours, the recipient cell would reflect a value of 1.50. If cell J226 is blank, I want the recipient cell to remain blank.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Desu Nota from Columbus]

When you say Date/Time, what is the actual format?

I tested this using the format, custom> m/d/yyyy hh:mm, and this formula:

K226: =(j226-now()) and this gives you the time in days


Do you want the date "today" to update constantly? If yes, use now(), if you want a static "today" use today() in the formula instead.

Reverse the order if you want to change whether it is +/- or insert =abs(j226-now()) to always have a + answer

 

[fishin]

Thanks. I'd like the result of that action =(J226-NOW()) to take place and post conditionally in the recipient cell if the resulting value is equal or greater than zero but less than 1.60 and only if J226 is not blank. If J226 is blank, the recipient cell is to remain blank.

 

[Desu Nota from Columbus]

I can't do what you ask directly because that creates a circular reference I believe (or I just don't know excel well enough yet), but you can accomplish your request using a work around.


1. Create a blank column for the =j226-now()------Im going to assume for this example you create column K

2. Change the formula to say =if(j226="","",j226-now()) --this will leave K226 blank if nothing is in J266 (request #1)

3. In your "recipient cell" (wherever that is) use this formula:

=IF(AND(K226>=0,K226<=1.6),K226,"") (request #2, if your number is between 0 and 1.6 it will appear, if not it is left blank)

4. Hide the column you created in step 2