Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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") |
#2
|
|||
|
|||
Drat. Take out the line breaks first.
|
#3
|
|||
|
|||
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") |
#4
|
|||
|
|||
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") |
#6
|
|||
|
|||
or
=ROUNDUP(NOW(),0)-1/24*7-NOW() |
#7
|
|||
|
|||
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() |
#8
|
|||
|
|||
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() |
#9
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
empty cells in ranges | Excel Discussion (Misc queries) |