# Thread: Calculating time ... (XL97)

1. ## 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!

2. ## 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.

3. ## 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>

4. ## 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.

5. ## Re: Calculating time ... (XL97)

Oh! Here you go:

=17/24-MOD(NOW(),1)

6. ## 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!

7. ## 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/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=44222>this thread</A> for a continually updating in-cell clock.

8. ## 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 ...

9. ## Re: Calculating time ... (XL97)

="There are only "&TEXT(17/24-MOD(NOW(),1),"hh:ss")& " hours:seconds left until this miserable day is over!"

Remember that it doesn't update until the WB is recalculated.

10. ## Re: Calculating time ... (XL97)

="There are only "&TEXT(17/24-MOD(NOW(),1),"h")& " hours and "&TEXT(17/24-MOD(NOW(),1),"ss")&" seconds left until this miserable day is over!"

11. ## 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>

12. ## 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?!

13. ## Re: Calculating time ... (XL97)

Hmm, if you've got =Today()+17/24 in A1, =Now() in A2 and =A1-A2 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?

14. ## 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/24-MOD(NOW(),1),"h")& " hours, "&TEXT(17/24-MOD(NOW(),1),"m")& " minutes and "&TEXT(17/24-MOD(NOW(),1),"s")&" seconds left until this miserable day is over!"

15. ## 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!

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•