Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    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


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

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    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


  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  5. #5
    Uranium Lounger
    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/24-MOD(NOW(),1)
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    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


  7. #7
    Uranium Lounger
    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/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=44222>this thread</A> for a continually updating in-cell clock.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

  9. #9
    Uranium Lounger
    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/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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    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/24-MOD(NOW(),1),"h")& " hours and "&TEXT(17/24-MOD(NOW(),1),"ss")&" seconds left until this miserable day is over!"
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    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


  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    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


  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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?
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Uranium Lounger
    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/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!"
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    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


Page 1 of 3 123 LastLast

Posting Permissions

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