Show more than 23:59:59 hours with time calculations

[jennie]

Hi,

This is a basic countdown formula that shows the time left, it i
updated every time the worksheet is changed.

=IF(((G3+I3)>G1),((G3+I3)-G1),0)

G3 is the start date and time of the job (set in dd/mm/yyyy hh:m
format)
I3 is the number of hours estimated until the job is complete (set i
hh:mm format)

So added together G3 and I3 gives the date and time to be complete
by.

G1 shows the current date and time, using the NOW() formula (set i
dd/mm/yyyy hh:mm format)

So taking away G1 from G3 + I3 gives the time left for the job to b
completed.

However, by using an IF function to check that the finish date and tim
is greater than the current date and time, and only performing th
calculation ((G3 + I3) - G1) (giving the time left) if it is, an
putting the value 0 if it is not, removes the error got if time left i
negative.
=IF(((G3+I3)>G1),((G3+I3)-G1),0)

Now for the problem(s)!

I3, the estimated time until the job is complete is always given i
hours, however this can be greater than 23:59:59 and I would like th
cell to show for example 48:00 if the estimate is 48 hours.

Also the time left (the output from the formula) can be greater tha
23:59:59 I would also like this to give the result in hours an
minutes, eg 39:55

Also, is there a way (using macros) to get the spreadsheet to run
macro (called UpdateSheet() ) at regular intervals eg every 5 mins?

Any help would be great
Thanks in advance
Jenni

 

[Frank Kabel]

Hi
use the custom format [hh]:mm for the target cell. The '[' brackets are
essential to prevent the rollover after 24 hours.

For the second question you may use the OnTime method to run macros
periodically. See
http://www.cpearson.com/excel/ontime.htm
for more details

 

[jennie]

Thank you,

I have got the times working, now I am going to have a look at th
automatic updating.

Thanks for you help
Jenni