Results 1 to 15 of 34
Thread: Calculating time ... (XL97)

20011022, 13:50 #1
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 Thanked 0 Times in 0 Posts
Calculating time ... (XL97)
Hi all.
I'm not very good with Excel and I've been trying to work out how to do this for about half an hour with no luck! I have two cells, one has 17:00 in it (using time() or just typing it, neither seems to work), the other the current time (using now()); I'm trying to find a calculation that will tell me how many hours and minutes it is until 17:00, ie deduct now from 17:00  but can I work it out?! Can I heck!
I'm sure I'm missing something stupid here, but any help would be greatly appreciated!
Many thanks in advance.Beryl M

20011022, 14:14 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Calculating time ... (XL97)
Beryl, times are maintained in Excel as fractions of a day. So, 17:00 will return the equivalent of 17/24ths of a day. Now() returns the entire count of days and part of today since 1/1/1900. [Edited as I had the subtraction backwards] So you are trying to subtract <font color=red>17/24ths of one day from 37186.4 days </font color=red>(in my timezone). Now, the answer to your problem depends a little on what range of days you need to cover, but here's something to force your start time to always be yesterday, and may help you understand the issue better. Instead of entering 17:00 (or 17/24), enter this to force the time to be yesterday and provide a full count of days:
=DATE(YEAR(NOW()),MONTH(NOW()), DAY(NOW())1)+17/24
formatting all time cells as time.
Now your formula will work within a 24 hour period. To get it to work over a longer period, you need to adjust the DATE(YEAR(NOW()),MONTH(NOW()), DAY(NOW())1) part of the formula as appropriate to your needs.John ... I float in liquid gardens
UTC 7ąDS

20011022, 14:16 #3
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 Thanked 0 Times in 0 Posts
Re: Calculating time ... (XL97)
Thanks for the reply, and I do now understand a bit better how XL works with times, but what I want is a formula that will always tell me how many hours and minutes there are until 5pm today ... how long to hometime, in effect (that's not what it's for, but ...!), so that anytime I open this spreadsheet, it will say how many hours and minutes to 17:00.
I don't see how to take what you've given me, connect it to the time now and take one from the other ...?! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>Beryl M

20011022, 14:18 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,349
 Thanks
 4
 Thanked 228 Times in 210 Posts
Re: Calculating time ... (XL97)
Hi John,
FWIW you could also use =Today()+17/24
though it may be worth noting that this will still not produce a real time clock  i.e. it will only give the difference at the time the workbook is last calculated.Regards,
Rory
Microsoft MVP  Excel

20011022, 14:20 #5
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Calculating time ... (XL97)
Oh! Here you go:
=17/24MOD(NOW(),1)John ... I float in liquid gardens
UTC 7ąDS

20011022, 14:25 #6
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 Thanked 0 Times in 0 Posts
Re: Calculating time ... (XL97)
Brilliant! You're an angel!
Would I be pushing it if I asked how to display just the hours or just the minutes, so I could have something that said "There are now ... hours and ... minutes until 5pm" or something similar? I found that formatting the cell containing your formula for hours works, but using the same with m for minutes doesn't  I think it thinks I mean months, perhaps?
Many thanks!Beryl M

20011022, 14:26 #7
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Calculating time ... (XL97)
Beryl, Rory's formula is better than mine and also he's correct that it will only update when the WB recalcs. See <A target="_blank" HREF=http://www.wopr.com/cgibin/w3t/showflat.pl?Cat=&Board=xl&Number=44222>this thread</A> for a continually updating incell clock.
John ... I float in liquid gardens
UTC 7ąDS

20011022, 14:29 #8
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Calculating time ... (XL97)
Whoa, wait a minute, Rory's formula returns the negative count of days since 1/1/1900, and the time difference; my formula does appear better ...
John ... I float in liquid gardens
UTC 7ąDS

20011022, 14:31 #9
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Calculating time ... (XL97)
A little license taken:
="There are only "&TEXT(17/24MOD(NOW(),1),"hh:ss")& " hours:seconds left until this miserable day is over!"
Remember that it doesn't update until the WB is recalculated.John ... I float in liquid gardens
UTC 7ąDS

20011022, 14:34 #10
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Calculating time ... (XL97)
It would help if I read your request more carefully:
="There are only "&TEXT(17/24MOD(NOW(),1),"h")& " hours and "&TEXT(17/24MOD(NOW(),1),"ss")&" seconds left until this miserable day is over!"John ... I float in liquid gardens
UTC 7ąDS

20011022, 14:39 #11
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 Thanked 0 Times in 0 Posts
Re: Calculating time ... (XL97)
That was it, John! Great! And I've learnt a few more bits in there that I can quite easily see being useful in other places, too ...
Many thanks! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15> <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> <img src=/S/cool.gif border=0 alt=cool width=15 height=15> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21> <img src=/S/bow.gif border=0 alt=bow width=15 height=15>Beryl M

20011022, 14:43 #12
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 Thanked 0 Times in 0 Posts
Re: Calculating time ... (XL97)
Leastways, I thought it was  but now I find that it still doesn't like telling me how many minutes are left ... hours fine, seconds too, but at 4:40pm (here) it's telling me I have one minute left to five o'clock ...
Where am I going wrong?!Beryl M

20011022, 14:43 #13
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,349
 Thanks
 4
 Thanked 228 Times in 210 Posts
Re: Calculating time ... (XL97)
Hmm, if you've got =Today()+17/24 in A1, =Now() in A2 and =A1A2 in A3, all formatted as time, that should always give hours and minutes between now and 17:00 today by my reckoning (notwithstanding the previous comments about updating). Is that not what you found?
Regards,
Rory
Microsoft MVP  Excel

20011022, 14:49 #14
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Calculating time ... (XL97)
You are right Beryl; people keep calling me wanting me to do my actual work, and I messed that up. Try this:
="There are only "&TEXT(17/24MOD(NOW(),1),"h")& " hours, "&TEXT(17/24MOD(NOW(),1),"m")& " minutes and "&TEXT(17/24MOD(NOW(),1),"s")&" seconds left until this miserable day is over!"John ... I float in liquid gardens
UTC 7ąDS

20011022, 14:50 #15
 Join Date
 Jun 2001
 Location
 Basingstoke, Hampshire, United Kingdom
 Posts
 1,845
 Thanks
 11
 Thanked 0 Times in 0 Posts
Re: Calculating time ... (XL97)
Don't worry, I had extrapolated the minutes bit from the hours and seconds you gave  but the "m" just says 1, no matter how many actual minutes; I think it's talking about months instead of minutes!
And I sympathise about the work  this is actually for work, so It's easier for me!Beryl M