# If/Then Statement Help on Tardies

#### squeakums

##### Well-known Member
I need help on an if/then statement. I am working with columns A and B. Column A is Actual Scheduled Time and Column B is Actual Worked Time - I need to write an if/then statement - if b1 is 1 minute or more tardy to a1 then cell will equal = 1

Thanks!

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Something along the lines of

=IF(OR(A1-B1>1,A1-B1<-1),1,"Something Else")

Try

=IF(B1-A1>1/1440,1,"")

Excel Workbook
ABC
18:008:011
28:008:031
38:008:00
48:008:011
58:008:00
68:009:001
78:008:111
88:008:00
98:008:00
108:008:131
118:0010:001
128:008:00
138:008:00
148:0011:001
158:008:00
168:008:00
178:008:00
188:008:021
198:008:00
208:008:00
218:008:00
Sheet3

If my understanding of Tardy is correct, you mean like this?
What happens if a Cell in column B is less than it's partner in A?

hope it helps

These are awesome responses. I appreciate your help. Okay, so my next step is this. I am creating an if/then statement for each parameter:

Tardy: =IF(D11-C11>1/1440,1,"")

then I need to setup a formula if great than 1 hour added to this formula - if greater than 1 hour leave blank

or if column E11 has a number, leave blank

I also need an if/then statement if over 1 hour place a 1. The 1 above is if over 1 minute.

Thanks for all of your help so far

Now I'm confused

Can you post some examples of what you mean?

Okay - I need a if/then statement that says if C11 has something and D11 doesn't have something then = 1

C11 D11 E

8:00AM 1

Then, I need a formula that says if E is blank then use =IF(D11-C11>1/1440,1,"")

Lastly, I need a formula that says if D11-C11>1 hour then 1.

What does the 1440 stand for inthe if/then formula that you wrote by the way?

I am trying to setup different parameters for the following:

1st Formula:
Anything 1 minute over (such as lunch was 60 minutes but they took 61 minutes) = 1

2nd Formula:
Anything 1 hour and over (such as start time should be 8:00 but signed in at 9:00) = 1

3rd Formula:
1 minute or early (such as end time should be 4:30 but left at 4:29) = 1

4th Formula:
1st call in is an event – any call ins or nc/ns consecutive to that 1st day receive no additional event and call ins or nc/ns, off one day and then called in or nc/ns is only 1 event as well.

All of the formulas will be in different cells of course.

Perhaps

<b>Sheet18</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">08:00</td><td style="text-align:right; ">08:01</td><td > </td><td style="text-align:right; ">1</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E11</td><td >=IF(AND<span style=' color:008000; '>(C11<>"",D11="")</span>,1,"")</td></tr><tr><td >F11</td><td >=IF(AND<span style=' color:008000; '>(E11="",D11-C11>1/1440)</span>,1,"")</td></tr><tr><td >G11</td><td >=IF(AND<span style=' color:008000; '>(E11="",D11-C11>1/24)</span>,1,"")</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

The 1/1440 is a minute (there are 1440 minutes in a day and Excel uses fractions of a day to express times - e.g. 12:00 is treated as 0.5 internally).

C11 D11 E11
Sch Act Point
8:00 AM 9:00 AM 1

If D11 is more than 1 hour past C11 then E11 will equal 1.

Replies
6
Views
293
Replies
3
Views
177
Replies
2
Views
162
Replies
11
Views
676
Replies
3
Views
305

1,196,100
Messages
6,013,452
Members
441,767
Latest member
Craigh4444

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back