time formatting and time categorizing (vlookup or data validation)

[MarianneR]

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

First, although I searched the forum for similar issues, I could no
understand how to solve my problem with the time format. I am making
list of times: 15 minutes, 30 minutes, 45 minutes, and 60 minutes.
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
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 an
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.
want the on "time/late" column to either self populate with an i
statement or select from a list, using vlookup.

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

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

Any help would be greatly appreciated!!

Thanks in advance,
Mariann

 

[excel_googler]

Hi..

You may have to convert the time entered as Decimal for clarity i
results..

A1 10:15
B1 10:45

in C1 & D1.. u may have to apply following formula

in C1 (A1 * 24)*60
in D1 (B1 * 24)*60

then,
in E1.. u may enter D1-C1

..this may not match ur expectations..but it may help..if not..ignor

 

[Biff]

Hi!

When you want to enter a time of 15 minutes you have to
enter it as a time in h:m format: 0:15:00 or 0:15. Then
the format mm:ss will work.

Now then, I see that the time difference can be negative
but you cannot display a negative time format using the
default time format that Excel uses. You either have to
use decimal values or use the 1904 date system. Also, in
the one example the person was 5 mins late yet you have
them designated as on time?

What exactly are ALL the designations?

Biff

 

[Arvi Laanemets]

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"))

 

[O'C]

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