Time Calculation
 

Hi

I have 28-Jul-05 15:40:11 in cell D3 and would like to calculate the
period remaining until it is reached. I tried using NOW in another cell
and subtracting them but "VALUE" is always returned.

Is there a formula to return it in DD/MM or DD/MM/SS ?

TIA
Tim

Think this works?

=TEXT(D3-NOW(),"dd:hh:mm")

Regards,
A

"Tim Mills" wrote:

Hi

I have 28-Jul-05 15:40:11 in cell D3 and would like to calculate the
period remaining until it is reached. I tried using NOW in another cell
and subtracting them but "VALUE" is always returned.

Is there a formula to return it in DD/MM or DD/MM/SS ?

TIA
Tim

not sure why it would return value, unless the value in D3 is actually
text in which case you'll have to convert it to a date.

if you want to know how long, to the second, is left, you will have to
create a string yourself, such as:

=DAY(D3-NOW()) & " days, " & HOUR(D3-NOW()) & " hours, " &
MINUTE(D3-NOW()) & " minutes, " & SECOND(D3-NOW()) & " seconds"

The pattern should be obvious so you can make it as complex as you
need.

Rob

I still get the "VALUE" error message.
Could it be the format of either cell?
Tim


"aristotle" wrote:

Think this works?

=TEXT(D3-NOW(),"dd:hh:mm")

Regards,
A

"Tim Mills" wrote:

Hi

I have 28-Jul-05 15:40:11 in cell D3 and would like to calculate the
period remaining until it is reached. I tried using NOW in another cell
and subtracting them but "VALUE" is always returned.

Is there a formula to return it in DD/MM or DD/MM/SS ?

TIA
Tim

I'm not sure whether you want to calculate the difference between two dates,
two times or both. I'm assuming you just want to calculate the difference
between two dates (Below)

Cell A1 =Today()
Cell A2 = your target date
Cell A3 =Days360(A1,A2)
Cell A4 =Concatenate(A3," Days to go!")

If this isn't what your after I suggest doing this in VBA instead. I have
some sample code if you want it. Let me know.

Jim Nicholls




************************************************** ****

"Tim Mills" wrote:

Hi

I have 28-Jul-05 15:40:11 in cell D3 and would like to calculate the
period remaining until it is reached. I tried using NOW in another cell
and subtracting them but "VALUE" is always returned.

Is there a formula to return it in DD/MM or DD/MM/SS ?

TIA
Tim

=DAYS360(NOW(),LEFT(D3,9),0) would calculate the difference in days assuming
the date is always in the same format (i.e. always the first 9 characters)

calculating the difference in hours, minutes etc. is a bit more complicated

Yoav.

"Tim Mills" wrote:

Hi

I have 28-Jul-05 15:40:11 in cell D3 and would like to calculate the
period remaining until it is reached. I tried using NOW in another cell
and subtracting them but "VALUE" is always returned.

Is there a formula to return it in DD/MM or DD/MM/SS ?

TIA
Tim

i would suspect that the "VALUE" error comes from the value in D3 being
text. If you select the value for editing (select cell and press F2)
and then press enter, it should recognise it as a proper date and the
function should work.

Aristotle's idea is the best, i think. and to build on it try the
following:

=TEXT(D3-NOW(),"d \da\y\s, h \hour\s, m \minut\e\s an\d s \s\econ\d\s")

regards
Rob