# Thread: Excel List formula (2003)

1. ## Excel List formula (2003)

I work in the accounting department of a small city newspaper (circ. 30,000). I am recording "on time" data for the times that news pages go to the press room. If the pages are turned before 12:00 am, they are "on time". If they are turned after midnight, they are "late". I have a field where I record the date and time that the page is turned. It's tricky because the page may be turned before midnight, so the date and time might be 2/10/05 11:45:00 PM. If after midnight the date and time will be like 2/11/05 0:15:00 AM.

I would like to use some kind of "if-then" formula to calculate whether the page is turned on time or late. I'm imagining something like:

"if the page is turned before midnight, print "on time" in blue; if the page is turned after midnight, print "LATE" in red."

I've looked in my Excel 2003 Formulas book and in the posts on this site, but I haven't seen anything like what I want.

Can you help?

2. ## Re: Excel List formula (2003)

How do you tell if it it "early" for the next day?

If it comes in at noon, is it late for yesterday or early for today?

Steve

3. ## Re: Excel List formula (2003)

Do you have a reference date in the worksheet to compare the times with? In theory, 2/11/05 0:15:00 could be an extremely early time for 2/11/05. Or is a time before midday always a late time for the previous day, and a time after midday "on time" for today?

4. ## Re: Excel List formula (2003)

Sorry, I forget that not everyone knows the newspaper business.

We are a morning newspaper, which means we have to print the paper in the wee hours of the morning in order to process and deliver by 6:00 AM. Our newsroom and pressroom staff work what is normally thought of as 3rd shift hours. We have a press start deadline of 12:30 AM. The newsroom has two deadlines: Editorial pages must be to the press room by 12:00 AM or they are late; Sports pages must be to the press room by 12:15 AM or they are late. If the pages are late, the press might not start on time. If the press doesn't start on time, the papers will be late to the Mailroom. If the Mailroom doesn't get the papers on time, the trucks will be late delivering the bundles; then our customers don't get their morning paper, and the Circulation department gets all kinds of complaint calls.

To give a more concrete example:

Today's edition is dated 2/10/05. The Editorial pages were sent to the press room at 11:55 PM on 2/9/05 (on time); the sports pages were sent to the press room at 12:15 AM 2/10/05 (on time). The press run started at 12:30 AM 2/10/05 (on time).

I hope this helps to clarify things a bit.

In my previous post I didn't mention the two Newsroom deadlines because I didn't want to confuse the issue too much.

So if the Editorial pages are sent to the press room at or before 12:00 AM, they're on time.
If the Sports pages are sent to the press room at or before 12:15 AM they're on time

5. ## Re: Excel List formula (2003)

See if the attached workbook does what you want.

#### Posting Permissions

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