| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Modified IF formula for military time
Hi,
I posted a question earlier to figure out a formula that tests whether an employee was present at a certain hour. My formula so far is: =IF(OR(AND($a2<=$b2,c$1=$a2,$b2c$1),AND($a2=$b2 ,OR(c$1=$a2,c$1<$b2))),1,0) The result is: Time In Time Out Present @hr 5 Present @hr 20 Employee 1 05:55 20:01 Yes Yes Employee 2 22:30 07:15 Yes No Employee 3 06:05 08:01 No No I need to change the formula so that if Employee 1 is early 5 minutes he is not included in the previous hour, and if Employee 1 stays 1 minute late, he is not included in hour 20. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Modified IF formula for military time
Sorry, the margin of error should be 15 minutes. Thanks!
"kmariab" wrote: Hi, I posted a question earlier to figure out a formula that tests whether an employee was present at a certain hour. My formula so far is: =IF(OR(AND($a2<=$b2,c$1=$a2,$b2c$1),AND($a2=$b2 ,OR(c$1=$a2,c$1<$b2))),1,0) The result is: Time In Time Out Present @hr 5 Present @hr 20 Employee 1 05:55 20:01 Yes Yes Employee 2 22:30 07:15 Yes No Employee 3 06:05 08:01 No No I need to change the formula so that if Employee 1 is early 5 minutes he is not included in the previous hour, and if Employee 1 stays 1 minute late, he is not included in hour 20. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Modified IF formula for military time
What's in cells A2, B2 & C1, firstly?
"kmariab" wrote: Hi, I posted a question earlier to figure out a formula that tests whether an employee was present at a certain hour. My formula so far is: =IF(OR(AND($a2<=$b2,c$1=$a2,$b2c$1),AND($a2=$b2 ,OR(c$1=$a2,c$1<$b2))),1,0) The result is: Time In Time Out Present @hr 5 Present @hr 20 Employee 1 05:55 20:01 Yes Yes Employee 2 22:30 07:15 Yes No Employee 3 06:05 08:01 No No I need to change the formula so that if Employee 1 is early 5 minutes he is not included in the previous hour, and if Employee 1 stays 1 minute late, he is not included in hour 20. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Modified IF formula for military time
A2= Time In
B2= Time Out C1=Test Time (Present at hr X) "Harimau" wrote: What's in cells A2, B2 & C1, firstly? "kmariab" wrote: Hi, I posted a question earlier to figure out a formula that tests whether an employee was present at a certain hour. My formula so far is: =IF(OR(AND($a2<=$b2,c$1=$a2,$b2c$1),AND($a2=$b2 ,OR(c$1=$a2,c$1<$b2))),1,0) The result is: Time In Time Out Present @hr 5 Present @hr 20 Employee 1 05:55 20:01 Yes Yes Employee 2 22:30 07:15 Yes No Employee 3 06:05 08:01 No No I need to change the formula so that if Employee 1 is early 5 minutes he is not included in the previous hour, and if Employee 1 stays 1 minute late, he is not included in hour 20. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Converting Standard Time to Military Time in Excel | Excel Discussion (Misc queries) | |||
| military time "if" formula | Excel Discussion (Misc queries) | |||
| formula for converting military time to standard time, etc | Excel Discussion (Misc queries) | |||
| Converting Standard Time into Military Time | Excel Discussion (Misc queries) | |||
| Show timesheet time in and out in regular time versus military tim | Excel Worksheet Functions | |||
Linear Mode
