How long until 5:00?
 

Copy and paste the following into an Excel cell.

=INT((INT(NOW())+(17/24)-NOW())*24)&"
"&IF(INT((INT(NOW())+(17/24)-NOW())*24)=1,"hour","hours")&"
"&INT((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW())*24)))*60)&"
"&IF(INT((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW())*24)))*60)=1,"minute","minutes")&"
"&INT(((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW())*24)))*60-INT((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW())*24)))*60))*60)&"
"&IF(INT(((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW())*24)))*60-INT((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW())*24)))*60))*60)=1,"second","seconds")

Drat. Take out the line breaks first.

Hi
and your question is?

Just a remark
instead of INT(NOW())
use
TODAY()

--
Regards
Frank Kabel
Frankfurt, Germany

"Ray_Johnson" schrieb im
Newsbeitrag ...
Copy and paste the following into an Excel cell.

=INT((INT(NOW())+(17/24)-NOW())*24)&"
"&IF(INT((INT(NOW())+(17/24)-NOW())*24)=1,"hour","hours")&"

"&INT((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW())*
24)))*60)&"

"&IF(INT((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW(
))*24)))*60)=1,"minute","minutes")&"

"&INT(((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW())
*24)))*60-INT((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)
-NOW())*24)))*60))*60)&"

"&IF(INT(((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW
())*24)))*60-INT((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/
24)-NOW())*24)))*60))*60)=1,"second","seconds")

Just another way:

=SUBSTITUTE(SUBSTITUTE(TEXT(1-MOD(NOW()+7/24,1),"h ""hour%"" m
""minute&"" s ""second"""),"%",IF(HOUR(1-MOD(NOW()+7/24,1))=1,"","s")),
"&",IF(MINUTE(1-MOD(NOW()+7/24,1))=1,"","s"))&IF(SECOND(1-MOD(NOW()+7/24,
1))=1,"","s")

or, if you define (Insert/Name/Define) "t" = 1-MOD(NOW()+7/24,1), and
"f" as "h ""hour%"" m ""minute&"" s ""second""" then

=SUBSTITUTE(SUBSTITUTE(TEXT(t,f), "%", IF(HOUR(t)=1,"","s")), "&",
IF(MINUTE(t)=1, "", "s")) & IF(SECOND(t)=1, "", "s")

In article ,
"Ray_Johnson" wrote:

Copy and paste the following into an Excel cell.

=INT((INT(NOW())+(17/24)-NOW())*24)&"
"&IF(INT((INT(NOW())+(17/24)-NOW())*24)=1,"hour","hours")&"
"&INT((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW())*24)))*6
0)&"
"&IF(INT((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW())*24))
)*60)=1,"minute","minutes")&"
"&INT(((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW())*24)))*
60-INT((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW())*24)))*
60))*60)&"
"&IF(INT(((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW())*24)
))*60-INT((((INT(NOW())+(17/24)-NOW())*24)-(INT((INT(NOW())+(17/24)-NOW())*24)
))*60))*60)=1,"second","seconds")

a1=now()
a2=roundup(a1,0)
a3=a2-1/24*7
a4=a3-a1
format a4 as h:mm:ss

or

=ROUNDUP(NOW(),0)-1/24*7-NOW()

That works for times between midnight and 17:00, but gives negative
times between 17:00:01 and 23:59:59, which will display as #######
unless the 1904 date system is used.

That may be acceptable for the OP (his goes negative after 17:00 too), I
don't know, but since his formula included words rather than just ":"s,
I assume not.


In article ,
"MHoffmeier" wrote:

=ROUNDUP(NOW(),0)-1/24*7-NOW()

Sorry, the OP had expired in my reader.
How about
=IF(NOW()(ROUNDUP(NOW(),0)-1/24*7),ROUNDUP(NOW(),0)+1-1/24*7-NOW(),ROUNDUP(NOW(),0)-1/24*7-NOW())
Seems to work
"JE McGimpsey" wrote in message
...
That works for times between midnight and 17:00, but gives negative
times between 17:00:01 and 23:59:59, which will display as #######
unless the 1904 date system is used.

That may be acceptable for the OP (his goes negative after 17:00 too), I
don't know, but since his formula included words rather than just ":"s,
I assume not.


In article ,
"MHoffmeier" wrote:

=ROUNDUP(NOW(),0)-1/24*7-NOW()

In article ,
"MHoffmeier" wrote:

Sorry, the OP had expired in my reader.

You can always find it in the archives...

http://groups.google.com/advanced_gr...=group:*excel*

How about
=IF(NOW()(ROUNDUP(NOW(),0)-1/24*7),ROUNDUP(NOW(),0)+1-1/24*7-NOW(),RO
UNDUP(NO W(),0)-1/24*7-NOW()) Seems to work

The salient thing to me about the original post seemed to be using the
words "hour(s)", "minute(s)", and "second(s)" conditionally, not just
getting the time remaining.