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")
0
11/30/2004 8:27:04 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
329 Views

Similar Articles

[PageSpeed] 29

Drat.  Take out the line breaks first.
0
11/30/2004 8:29:10 PM
Hi
and your question is?

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Ray_Johnson" <RayJohnson@discussions.microsoft.com> schrieb im
Newsbeitrag news:B915BE7F-6B00-4037-9E2F-FCE4479A647A@microsoft.com...
> 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")

0
frank.kabel (11126)
11/30/2004 8:33:34 PM
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 <B915BE7F-6B00-4037-9E2F-FCE4479A647A@microsoft.com>,
 "Ray_Johnson" <RayJohnson@discussions.microsoft.com> 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")
0
jemcgimpsey (6723)
11/30/2004 11:27:21 PM
a1=now()
a2=roundup(a1,0)
a3=a2-1/24*7
a4=a3-a1
format a4 as h:mm:ss


0
MHoffmeier
12/2/2004 9:22:42 PM
or

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


0
MHoffmeier
12/2/2004 9:33:15 PM
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 <coo1mp$69tp$1@news3.infoave.net>,
 "MHoffmeier" <mhoffmeier_spammenot_ok?@hargray.com> wrote:

> =ROUNDUP(NOW(),0)-1/24*7-NOW()
0
jemcgimpsey (6723)
12/3/2004 12:50:43 AM
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" <jemcgimpsey@mvps.org> wrote in message 
news:jemcgimpsey-3AEA0B.17504302122004@msnews.microsoft.com...
> 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 <coo1mp$69tp$1@news3.infoave.net>,
> "MHoffmeier" <mhoffmeier_spammenot_ok?@hargray.com> wrote:
>
>> =ROUNDUP(NOW(),0)-1/24*7-NOW() 


0
MHoffmeier
12/3/2004 2:02:28 PM
In article <coprlh$74k1$1@news3.infoave.net>,
 "MHoffmeier" <mhoffmeier_spammenot_ok?@hargray.com> wrote:

> Sorry, the OP had expired in my reader.

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

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.
0
jemcgimpsey (6723)
12/3/2004 2:24:34 PM
Reply:

Similar Artilces:

error LNK2001: unresolved external symbol "long __cdecl _com_dispatch_method(struct IDispatch *,long,unsigned short,unsigned short,void *,wchar_t const *,...)" (?_com_dispatch_method@@YAJPAUIDispatch@
Hi, I have installed Visual Studio Express 2005, and the platform SDK. I'm trying to compile, but get this linker error. The only reference I found on the web was a post of this person describing the possible cause: http://www.eggheadcafe.com/ng/microsoft.public.platformsdk.sdk_install/post20679802.asp Any clues, hints.. ? Leo Well, since you didn't bother to put the error message in the body of the text, it is a bit hard to read. Please do not put content in the headers. I only see the first segment of the header in my reader. A header that contains interesting informatio...

Outlook 2003 Calendar #5
I have been using Outlook 2000 for a couple of years now, but recently upgraded to Office 2003 Pro. OL2K's calendar consisted of two panes: the current day on the left side, and the full month on the right. Now on OL 2003 it seems I can only switch between day-view, week-view, or month-view. Is there an option to have a similar functionality as in OL2K - with both day and full month on one panel? (I checked Help, but couldn't find the answer). Thanks, Pat Sorry; I just went back to Outlook and noticed that the months (3 months now) are there -- now located on the LEFT side. Pl...

Outlook Express Outbox #5
I have a large mail in my outbox and the program freezes when I attempt to delete it. Any suggestions on how to purge this particular mail from the outbox? Dennis <anonymous@discussions.microsoft.com> wrote: > I have a large mail in my outbox and the program freezes > when I attempt to delete it. Any suggestions on how to > purge this particular mail from the outbox? Ask in an Outlook Express newsgroup. -- Brian Tillman ...

SEnding mail #5
Hi all, For some reason, Outlook has stopped sending mail. It all gets to the Outbox, but doesn't despatch. Mail is still received okay, on all accounts. Is there a setting I could have changed? Anyone any ideas/ TIA Bob Check your account properties on the server tab. you may need to enable authentication. Ask your ISP if they recently changed how they run their mail servers. Many have changed to require authentication to cut down on spam. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and fin...

Milo: What is microsoft.windowslive.translator.btn? Name too long
In "C:\Documents and Settings\[myname]\Application Data\Microsoft\Windows Live\Toolbar\Custom Buttons", there is a folder called microsoft.windowslive.translator.btn that is causing me to have trouble backing up my computer. I'm being told that that path or folder name is too long. At first I thought it was the path, but trying to copy microsoft.windowslive.translator.btn DIRECTLY into the MyDocuments folder caused the same error, so now I'm pretty sure that it’s the 36 characters in that folder name that is the problem. Sure enough, when I renamed a copy...

Long Filename Hunter
hello, is the anyone know of a program/freeware that can search the disk for files with "long" file names? it is laborious to drill down dozens of folders and hundreds/thousands of subfolders hunting down down long file names. for example I found a couple of tiles with filenames as long as a sentence, obviously using all the amount allotted for naming a file. thanks... ~db -- db���`�...�><)))�> DatabaseBen, Retired Professional - Systems Analyst - Database Developer - Accountancy - Veteran of the Armed Forces - Microsoft Partner...

OWA Login #5
I have two issues currently with OWA. 1. I have some users that login to OWA but are unable to reply, forward or delete any messages. These users belong to the same groups as users that are able to perform these tasks. Are there some group/user permissions that I have overlooked? 2. Is there any other solution for Outlook Web Access for organizations with multiple 2003 Exchange servers than making the primary server a front-end server? This functionality worked fine with 5.5. I have users in location B and C who can not access through OWA because my DNS MX record points to the Exchan...

Replace Cell Address in A Long Formula
From time to time, I need to do some change in some very long formula. For example: =TRIM(IF(ISERROR(FIND(" ",B46,FIND(" ",B46,1)+2)),MID(B46,FIND(" ",B46,1)+1,LEN(B46)),MID(B46,FIND(" ",B46,FIND(" ",B46)+2),LEN(B46)))) I want to replace all the "B46" with "F18". Is there a easier way? Thanks. One quick and dirty way is to Edit>Replace B46 with F18. HTH Anders Silven "plumstone" <plumstone@discussions.microsoft.com> skrev i meddelandet news:29A2448B-1055-4847-9779-EA0FDDA5C05D@microsoft.com... > From...

default tabs changed to .25" go back to .5 in Publisher
We have 5 pcs networked through a server. When we set default tabs in a publisher document to .25", they change back to .5. It is a pain to reset them every time. How can we get them to stay at the .25? ...

Long Long Long Nested If Function
Hello, I need some help on a very long nested if formula. Or at least thats the type that I have been trying to use to get the calculations on my spreadsheet. I have this formula which is working for me but it is only one of the 5 variables I need to include in it. 1) =IF(B5="cdy",IF(P23=20,1625,(IF(P23=40,1875,2200)))) to this formula I need to add =IF(B5="nwr",IF(P23=20,850,(IF(P23=40,935,1260)))) =IF(B5="lsp",IF(P23=20,1125,(IF(P23=40,1225,1550)))) =IF(B5="mtrl",IF(P23=20,1035,(IF(P23=40,1150,1500)))) =IF(B5="trto",IF(P...

Exchange 5.5: circular logging off, but can't do incremental backup
I'm (still) running Exchange 5.5 SP4, with circular logging turned off (verified this just a second ago), but when I try to do an incremental backup from a remote machine, I get a normal backup instead. Any ideas why? On 21 Feb 2007 13:21:36 -0800, "Ross Presser" <rpresser@gmail.com> wrote: >I'm (still) running Exchange 5.5 SP4, with circular logging turned off >(verified this just a second ago), but when I try to do an incremental >backup from a remote machine, I get a normal backup instead. Any ideas >why? Cuz you selected the wrong choice? :) So aft...

Kenmore Canister Allergen Filtration Vacuum Cleaner Bags Fits 5055, 50557, 50558, And Panasonic Style C-5
List Price:$19.99 Image: http://bestdeallocator.info/image.php?id=B0012O3ELK Best price found: http://bestdeallocator.info/index.php?id=B0012O3ELK I have three Kenmore canister vacuum cleaners that use bag 50558. These work fine in all of my cleaners. I have AMAZON PRIME and they got here the very next day. It sure beats driving ten miles to the Sears store. I will buy these again as they are exactly what I wanted. I only wish they offered a package with more than three. Product appears to work great. It took a little bit longer than I expected for delivery. These new HEPA vacuum bags ...

how long does outlook 2003 save sent items?
i just looked today and all of my sent items from the past 4 years are GONE!!! i do not use archiving, and i have checked my old archive folder for them, they are not there. any help would be much appreciated - r gatestoo <gatestoo@hotmail.com> wrote: > i just looked today and all of my sent items from the past 4 years are > GONE!!! Then you deleted them somehow or are hiding them with a view filter because Outlook doesn't delete anything automatically, unless you've instructed it to do so. -- Brian Tillman ...

Move Mailboxes: 5.5 Spanish to 5.5 English
Good morning everyone, I have a tricky maneuver to perform next month and I am trying to get all of my ducks in a row. Hope you guys (and gals) can help. The Story: I have two Exchange 5.5 SP4 mail servers running on Win2K in Mexico. Win 2K server and Exchange 5.5 have been installed in Spanish. The mail servers are not part of my domestic exchange Org, and they are in a different NT 4.0 domain. I need to bring all of these mailboxes into my domestic Exchange 5.5 Org. I plan on migrating the NT 4.0 domain user accounts to my Active Directory 2003, and then export the mailboxe...

exchange 5.5/2003 with mixed mode 2003 domain public folders
Hi, I have recently completed some work for a potential customer, I am recommending that they in place upgrade their existing NT 4 domain to 2003 based on various reasons that I do not want to go into here. However, they also want their single exchange 5.5 org in the same domain to be migrated at the same time. This will be a mixed mode 5.5 to 2003 migration. At this stage the domain will be in mixed mode and will not support universal security groups. The customer uses public folders quitre heavily, I do not know the exact amount, but the user base is 2500 approx. They use distribution list...

scientific notation to display 1.8 x 10-5 in Excel
Can Excel display scientiic notation as, say 3.00 x 10-12 as opposed to the number 3.00E-12? How about a helper cell: =SUBSTITUTE(TEXT(A1,"0.00E+00"),"E"," x 10") And you can use the original cell for calculations. feloniousmoney wrote: > > Can Excel display scientiic notation as, say 3.00 x 10-12 as opposed to the > number 3.00E-12? -- Dave Peterson ...

FRx should be compatible with right-to-left languages #5
FRx should be compatible with right-to-left languages ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=0b35ccb9-c35e-45cb-bb5c-487d8f80e71b&dg=microsoft.public.greatplai...

long, LONG anf LPARAM
Hi Guys, A very Basic Question.Can anyone tell me whats exactly the difference between long, LONG and LPARAM in windows.If they all are same then what was the need to having 3 different names.any idea..?? thanks and regards, Sada "sada" <sadashiv.borkar@gmail.com> wrote: >Hi Guys, > A very Basic Question.Can anyone tell me whats exactly the >difference between > long, LONG and LPARAM in windows.If they all are same then >what was the need to having 3 different names.any idea..?? "long" is a primitive type in C++. Windo...

Locking cells #5
Can I lock different cells say A2 A4 A6 after that open and close workbook anytime and never have to unlock them, and still be able to use all other cells in the workbook Dave By default all cells are locked when the sheet is protected. Hit CRTL + A(twice if xl2003) then Format>Cells>Protection. Uncheck "locked" and OK. Select the cells you wish to lock and Format>Cells>Protection. Check "locked" and OK. Now Tools>Protection>Protect Sheet. Save the workbook. Gord Dibben Excel MVP On Fri, 18 Feb 2005 16:40:12 GMT, "Dave Elenor" &...

Long Print Times w/ Publisher mail merge
I frequently have mail merge documents cretaed in Pub 2007. They work great, but it takes forever publisher to release/spool these documents to the printer, and for the documents to print. I think I understand why this happens, what I am after is a way to print faster. Does anyone have any tricks to reduce the file size that is sent to the printer? Surely there must be a way around this, a way to print these mail merges faster. Anyone? Do you have a lot of images? Gradients? Is your printer driver current? Have you looked at the printer settings? Can you change the spooler settings, do...

migrate 13.8GB from 5.5 make more than 16Gb !!!
Hi, I'm using the Exchange 2003 migration tool to migrate my 350 accounts, the priv file on 5.5 was 13.8GB big and we can never finish the migration because the new priv file is bigger than 17GB before migrating 80% of the accounts! Do you know why ? and how I could resolve my problem ? Thanks in advance Chris Migration undoes "Single Instance Storage", so this is probably what happened. Exchange 2003 SP2 raises the limit to 75gb, but that won't release until Sept at the earliest (and given their track record for slippage, might well be much later than that.) &...

How long is too long for a post in this forum?
I tried to send the text of an error window that popped up in Excel, along with some specific questions. I learned that one is not allowed to insert a JPG, so I attached a couple. Then I got the too long message. I trimmed up the body of the message and removed one JPG attachment and tried again. This time, the system gave me a similar too long message and deleted my outboxed message. Each JPG file was only 60KB in size So how long is too long, and how can we tell in advance of the system deleting the whole message? Please do NOT attach anything to posts here. You will NOT make ...

Top 5 records per group
Hi, I have a table (tblPrices) which contains the following fields: Manufacturer, SoldPrice, OrderID What I want is a query to return the 5 highest SoldPrices for each Manufacturer. I have tried the suggested sql in Allen Browne's website but can't get it to work. Can anyone advise what the query (& subquery) would look like,l with the above table and field names. I'm confident that if I can get a working example I'll understand the principle better. Many thanks......Jason You can rank by group or, for a relatively small table, use a subquery like: SELECT a....

long datatype to date string
Hello, In my cell I have a long datatype value, like 1046101500. Now I want to have this converted to a date string like 'dd/m'/yyyy hh:mm:ss', so my example would be converted to '24/02/2003 15:45:00'. Is this possible? Andy Candy How does your data type correspond to the date? Where is the year? Month? etc. Andy. "Candy" <andy_hilvenNOSPAMPLEASE@hotmail.com> wrote in message news:3f729cad$0$814$ba620e4c@reader2.news.skynet.be... > Hello, > > In my cell I have a long datatype value, like 1046101500. Now I want to have > this converted to...

Exchange 5.5 can't send and receive to some domains
Exchange 5.5 can't send and receive to some domains NT 4.0 Exchane 5.5 A couple of domains have had trouble sending email to my 5.5 server, and at least one we can't sent to them or receive from them. At least one other reports that emails take 20 hours to reach my server. Everything else seems fine with the server with all other domains. It is a very small sample for which this occurs. Are there any tests I can run or something I can try? mick2767@hotmail.com Posting the NDRs would be helpful. Anything in the application log? <mick2767@hotmail.com> wrote in messa...