Here's a formula for a time card I use. I've always hated having to type the
":" into the time standards.

IN OUT LUNCH TOTAL HOURS
Mon 15-Nov-04 0730 1530 0030 7.5

Formula in E2:

=IF($B2="","",(TIME(LEFT(C2,2),RIGHT(C2,2),0)-TIME(LEFT(B2,2),RIGHT(B2,2),0)-IF(D2="",0,TIME(LEFT(D2,2),RIGHT(D2,2),0)))*24)

If you wanted minutes you would just thorw on a *60 at the end.

The only draw back is is that you have to use four characters all the time.

Hope you find your answers,
O'C

"MarianneR" wrote:


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