Calculating negative time & creating scrol bars for fixed size txt box.

[Jonathan hill via OfficeKB.com]

2 probs:
first - I'm trying to monitor arriving times by entering sched & actual arrival times into a table & using a formular to calculate if late or early etc e.g.
STA = 04:50
ATA = 04:45.
I have the cells in time format. If the actual arrival time is 04:45 OR 04:55, how do I create a formular to show positive or negative time?
I.e: 00:05(if its 5 min late) or -00:05(if 5 min early)
So far, it returns to show 5 min late as 00:05 (Great!)but early returns as ######. (no good to me!!)

Secoundly - I want to have a fixed size box that I can enter free flowing data or txt in to & then have a scrol on the side once the initial space is filled.....exactly the same as this box has just done when I exceeded origional size !! (I can create boxes OR scrol bars but not both !)
Help will be much appreciated so thx in advance. jon

 

[Jonathan hill via OfficeKB.com]

I think I'm half way to answering the 1st Q but need further help pls: If I use the following, I can work out the difference in mins between the 2 times - what it doesn't tell me is if this is early or late. Can i add colour (Red & Green) to distinguish early & late or even a minus sign??

=IF(A1>B1,B1+1-A1,B1-A1)

This is now doing my head in !!!.....thx again.

 

[Dave Peterson]

Do you use dates in this workbook?

If no, then you may want to toggle:
tools|options|calculation tab|1904 date system

This allows you to show negative time/dates.

If you do use dates, watch out. The existing dates will have 1462 days added to
each (4 years and a day).

You can adjust this if you want.

(from a previous post)

I like to keep my base date as 1900. If you do to, maybe this saved post will
help:


One workbook was using a base year of 1900 and the other was using 1904.
(tools|options|calculation tab|1904 date system)

One way to add those four years back is to find an empty cell, put 1462 into
that cell.

Copy that cell.

Select your range that contains the dates. Edit|PasteSpecial|click Add (in the
operation box).

You may have to reformat the cell as a date (mine turned to a 5 digit number).
But it should work.

You may want to do it against a copy...just in case.

(I'm not sure which one you'll fix. You may want to edit|pastespecial|click
subtract.)

Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as
the base date.

====================================

And if you copy dates to/from this workbook to/from a workbook with a different
date base, those dates will need to be adjusted.


============
#2. Maybe a Textbox from the Control Toolbox toolbar???

right click on it and chose properties.
Check
WordWrap (true)
Multiline (true)
Scrollbars (fmScrollBarsVertical)

 

[Jonathan hill via OfficeKB.com]

Thank you very much Dave - I'm just back from Hols & put it all in place
thx to you !!

Thyx again ! jon

 

[Dave Peterson]

I have no memory of what this was about, but I'm glad to hear it worked!