Calculating time … (XL97)

Topic

New Reply

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.

Reply | Quote

Replies

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 17/24ths of one day from 37186.4 days (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.

Reply | Quote

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 …?!

Reply | Quote

Oh! Here you go:

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

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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!”

Reply | Quote

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!

Reply | Quote

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?!

Reply | Quote

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!”

Reply | Quote

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!

Reply | Quote

Hi Beryl,
Try using:
=”There are only “&HOUR(17/24-MOD(NOW(),1))& ” hours and “&MINUTE(17/24-MOD(NOW(),1))&” minutes left until this miserable day is over!”
as you’re right, it refuses to recognize minutes (even if you use “Mm” which is supposed to be the custom format for minutes!)
Hope that helps.

Reply | Quote

It did indeed, and many thanks both to you and to John!

Now, about that other thread that John linked – with the updating clock – I tried that but although it runs fine when I open the document, as soon as it tries to rerun it tells me that the macro doesn’t exist! Where am I going wrong?!

Here’s what I did – I put a sub called workbooks_open in the ‘this workbook’ area of my workbook telling it to run UpdateClock, which is also in ‘this workbook’ area (works perfectly), but when it tries to rerun it says ‘The macro “u:holiday.xls!UpdateClock” cannot be found.” The path and document name are correct – why won’t it work?

I is confuggulated!

Reply | Quote

I’m guessing that you need to have all Legare Coleman’s code in your Personal.xls, because if you put it elsewhere and then close the workbook it ran from, Excel can’t find it anymore. You may want to check with Legare on that. BTW, yesterday afternoon I wrote the following to do what you want, but be warned: I can’t get working code to turn it off, so I may be asking Legare for help as well! Here’s my code, which you have to manually start; it doesn’t automatically load like Legare’s does:

Public Sub StatusBarClock()
Dim intRemHrs As Integer
Dim intRemMins As Integer
Dim strStatusTxt As String
Application.OnTime Now() + TimeValue(“0:00:10”), “StatusBarClock”
intRemHrs = 16 – Hour(Time())
intRemMins = 60 – Minute(Time())
strStatusTxt = “”
If intRemHrs Then
strStatusTxt = strStatusTxt & Format(intRemHrs, “#0″) & _
IIf(intRemHrs > 1, ” hours “, ” hour “)
End If
strStatusTxt = strStatusTxt & Format(intRemMins, “#0″) & _
IIf(intRemMins = 1, ” minute “, ” minutes “) & _
“left until 5.00 pm!”
Application.StatusBar = strStatusTxt
End Sub

Reply | Quote

[indent]

---

I’m guessing that you need to have all Legare Coleman’s code in your Personal.xls, because if you put it elsewhere and then close the workbook it ran from, Excel can’t find it anymore

---

[/indent]
Yes, but I put it in the spreadsheet that was showing it, and the code is actually called from within itself, so if it runs the first time how can it possibly not see itself to run the second time?!

Reply | Quote

If you are referrring to Legare’s code for an updating clock you should either place the code in a general module and call it from you Workbook_Open event OR change the UpdateClock code to the floowing :

Public Sub UpdateClock()
    Sheet1.Range("A1") = Format(Time(), "h:mm:ss")
    Application.OnTime Now() + TimeValue("00:00:30"), "ThisWorkbook.UpdateClock"
End Sub

Andrew C

Reply | Quote

Ah! That would be it!

Many thanks

Reply | Quote

I bet the “m” means months which means that the 01 is correct as the date is 01/01/1900

Reply | Quote

That was, indeed, the conclusion we came to!

Reply | Quote

Can you please explain how the mod function is helpful? I’ve seen you guys use it and I just can’t seem to figure it out. Like what’s the logic of it in this instance?

Thanks,
Wanting to Learn More

Reply | Quote

I posted a question last week and it one of the replies the MOD function was discussed. I am also studying it for further use. Look at the ss that was attached to my question. In those formulae the function is testing for the frequency of distribution of an amount. Sorry-have to figure out how to connect with that “this thread” link as shown above, else I’d link you directly.
Edited by WebGenii
Here is the link to the thread mentioned above.
– WG

Reply | Quote

It’s a simple thing that’s hard to explain! The modulo of a number and it’s divisor is the remainder of the number after subtraction of all even multiples of the divisor.

Think of MOD (modulo) as returning the remainder, after discarding the numbers that are an even multiple of the divisor. So =MOD(11, 9) returns 2, since 11/9 = 1 and a fraction (.222 repeater), the fraction is the reminder, so in this case the remainder is 2 (11-9); we are throwing away 9. =MOD(20,9) also returns two; 20/9 is 2.222; throw away the 2 and the remainder is again 2; because we are throwing away 18, all integers that evenly divide by the divisor. 20 divides by 9 twice, so throw away 18, and the remainder is the modulo (20 -9*2).

Now read Excel Help on =MOD() and see if makes more sense (or less?).

There are many uses, quite few in date and time arithmetic. A simple example is day of the week;

=MOD(TODAY(),7)

always returns a number between 0 and 6. Play with it yourself so you get the idea, by creating a formula like this:

cell A1 =TODAY()-10 cell B1 =MOD(A1,7)
cell A2 =A1+1 cell B2 =MOD(A2,7)
…
copy down for seven or eight cells, format column A cells as date, format column B cells as general, then examine the results. Finally, enter the following in cell C1 and copy it down

=CHOOSE(B1+1,”Weekend Warrior”,”Church”,”Extra Coffee”,”Plan”,”Work”,”Do Over”,”Leave Early”)

Does this help? (P.S., love the WC Fields quote.)

Reply | Quote

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.

Reply | Quote

Beryl, Rory’s formula is better than mine and also he’s correct that it will only update when the WB recalcs. See this thread for a continually updating in-cell clock.

Reply | Quote

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 …

Reply | Quote

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?

Reply | Quote

I found that I’m an idiot who didn’t notice that when I entered your formula the time format got changed, and when I reformatted the cell to time your way works fine.

Reply | Quote

Rory – help!!!!!!!!!!

I’ve just pulled the spreadsheet I created with your (and everyone else’s) help into XL2000 (it was in XL97 before), and now the formulas for working out the hours, minutes etc until 5:00pm don’t work anymore!

I just get a #NUM! error for the full sentence one (“There are now only … hours,” … etc), and fractions if I put the individual calculations in cells and calculate on those cells!

What’s going wrong?!

Reply | Quote

Off the top of my head, no idea! Can you post it? (or a scaled down version) I tested in Excel2k before posting so it should be OK.

Reply | Quote

Don’t worry, Rory – ignore me. It must be too late at night here (9pm) or something.

Numbers always do display better when the cell is formatted to display them properly …

Reply | Quote

Rory – No, I take that back, it still doesn’t work! I attach the spreadsheet in question (a little silly in places, I know, but good practice!).

The problem cells are O5 and Q5, and the problem is that as long as the time is before 5pm, they work fine; after 5pm I get the #NUM! error, about which the help is singularly unhelpful.

For example, in T5 is a formula which works fine (after 5pm) and returns the correct number of hours until 5pm tomorrow. In O5, however, where the identical formula is the ‘not matches’ side of an if function, it doesn’t. (And I haven’t even got to the minutes one, in Q5!)

I don’t understand!

Reply | Quote

Beryl,

I just had a look and applied general formatting to O5, and I get -5. The current format seems to m/d.

Hope that gets you back on track,

Andrew C

Reply | Quote

Thanks Andrew, but it made no difference on mine! By the way, what’s m/d?

Reply | Quote

Beryl,

m/d is month date, todays date would display as 5/1 (or 5/2 as it is now here), which is May 1st.

Try formatting as 0

Andrew C

Reply | Quote