Hi

1.
Whatever time format you use, you have to enter the time as h:m[:s]. So when
you entered 15:00, then Excel interpreted it as 15 hours. As you formatted
the cell as "mm:ss", 0 minutes and 0 seconds is displayed. To check this,
format the cell as "[m]:ss" - 15 hours=900 minutes, is it?
To enter 15 minutes, you have to enter 0:15 or 0:15

2.
=IF(INT(E2/(15/(24*60)))=0,"on time",IF(INT(E2/(15/(24*60)))3,"More than "
& INT((INT(E2/(15/(24*60)))+1)/4) & " hours
late",INT(E2/(15/(24*60)))*15&"-"&(INT(E2/(15/(24*60)))+1)*15&" minutes
late"))


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)



"MarianneR" wrote in message
...

Hi! I am so frustrated with the time formats on excel. I think they
make setting up functional worksheets so much more difficult. Enough
of the rant - onto the questions.

First, although I searched the forum for similar issues, I could not
understand how to solve my problem with the time format. I am making a
list of times: 15 minutes, 30 minutes, 45 minutes, and 60 minutes. I
formatted them as mm:ss and typed in 15:00 and the cells showed 00:00.
Is there anyway to get around this?

The reason I'm creating the list is because I'm trying to figure out a
way to categorize certain data. Here's what my spreadsheet *might*
look like...

A.......B..............C..................D....... ..........E...............
....................................F
date..name..appointment time..arrival time..difference between Appt and
Arrival...on time/late appointment

11/17/04..Jones..10:00..10:05...5:00...On time
11/17/04..Anderson..11:00..11:45..45:00..30-45 minutes late
11/17/04..Smith..11:30..NA..NA..No show


The "DIFFERENCE" column is calculated for difference in minutes. I
want the on "time/late" column to either self populate with an if
statement or select from a list, using vlookup.

I tried an if statement using fifteen minute intervals, and couldn't
get it to work. For example, it may have looked like this:
=if(E2="NA","No show", if(E200:60, "More than 60 minutes late",
if(E200:45.......

Now, I'm sure this is incorrect, but I am not sure how to fix it. Is
there an easy way to do what I want to do with VLOOKUP? Or, am I
perhaps misunderstanding the IF function?

Any help would be greatly appreciated!!

Thanks in advance,
Marianne


--
MarianneR
------------------------------------------------------------------------
MarianneR's Profile:
http://www.excelforum.com/member.php...fo&userid=6253
View this thread: http://www.excelforum.com/showthread...hreadid=314719