Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 18th 04, 05:38 AM
MarianneR
 
Posts: n/a
Default time formatting and time categorizing (vlookup or data validation)


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


  #2   Report Post  
Old November 18th 04, 06:11 AM
excel_googler
 
Posts: n/a
Default


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

  #3   Report Post  
Old November 18th 04, 06:18 AM
Biff
 
Posts: n/a
Default

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

.

  #4   Report Post  
Old November 18th 04, 06:27 AM
Arvi Laanemets
 
Posts: n/a
Default

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



  #5   Report Post  
Old November 18th 04, 03:24 PM
O'C
 
Posts: n/a
Default

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 10:02 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017