How long until 5:00?

R

Ray_Johnson

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")
 
F

Frank Kabel

Hi
and your question is?

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

--
Regards
Frank Kabel
Frankfurt, Germany

Ray_Johnson said:
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)&"
Click to expand...
"&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")
 
J

JE McGimpsey

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")
 
J

JE McGimpsey

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.
 
M

MHoffmeier

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
 
J

JE McGimpsey

MHoffmeier said:
Sorry, the OP had expired in my reader.
Click to expand...

You can always find it in the archives...

http://groups.google.com/advanced_group_search?q=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
Click to expand...

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.
 
Top