Excel Equation <> value

[jimjohndog]

I currently have a time recording spreadsheet where I need to isolate within a column values (hours) greater than 1.0 hour and less than 5.0 hour. Require assistance first formatting cell and then creating equation. Thanks James

 

[Momentman]

Can you give examples of values in that column and how they are formatted

 

[jimjohndog]

Can you give examples of values in that column and how they are formatted

My workers use a military clock and the time is totaled in column C which has the following equation:

=(( h1-G1)*24) This formula simply that takes a punch in time (h1) - a punch out time (g1) and within this column C I want to somehow either isolate those values that are greater than 1.0 hour and less than 5.0 hours within column C or have the results generated in an adjacent column. Thanks James

 

[shyy]

Yu should Post an example data with the desired end result.

 

[jimjohndog]

Yu should Post an example data with the desired end result.

I require an equation for column E to populate with values from Column D that are "greater than" 1.0 and "less than" 5.0. Also how do you formate that cell i.e. "numbers" -1234.10 prior to inserting equation. Thanks James


[TABLE="width: 542"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD] A[/TD]
[TD] B[/TD]
[TD] C[/TD]
[TD] D[/TD]
[TD] E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Names[/TD]
[TD]Punch IN[/TD]
[TD]Punch out[/TD]
[TD]Total Time[/TD]
[TD] >1.0 & < 5.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD="align: right"]9:45[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]5.25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sara Kay[/TD]
[TD="align: right"]8:15[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]3.75[/TD]
[TD="align: right"]3.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kevin Wilson [/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sue Blank[/TD]
[TD="align: right"]9:00[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Terry Brown[/TD]
[TD="align: right"]9:00[/TD]
[TD="align: right"]17:15[/TD]
[TD="align: right"]8.25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bill Connely [/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Betty Philbrick[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Momentman]

Something like this maybe

Excel 2010
	A	B	C	D	E
2	A	B	C	D	E
3	Names	Punch IN	Punch out	Total Time	>1.0 & < 5.0
4	John Smith	09:45	15:00	5.25
5	Sara Kay	08:15	12:00	3.75	3.75
6	Kevin Wilson	08:00	16:00	8
7	Sue Blank	09:00	10:00	1	1
8	Terry Brown	09:00	17:15	8.25
9	Bill Connely	08:00	11:00	3	3
10	Betty Philbrick	13:00	16:00	3	3
Sheet4

Cell Formulas
Range	Formula
E4	=IF(AND(D4>=1,D4<=5),D4,"")

Whats your question about cell formatting

 

[jimjohndog]

That works. Thank you very much. James