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

Hi..

You may have to convert the time entered as Decimal for clarity in
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..ignore


--
excel_googler
------------------------------------------------------------------------
excel_googler's Profile: http://www.excelforum.com/member.php...o&userid=16610
View this thread: http://www.excelforum.com/showthread...hreadid=314719

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

-----Original 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?
action=getinfo&userid=6253
View this thread:
http://www.excelforum.com/showthread...hreadid=314719

.

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

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