If/Then Statement Help on Tardies

squeakums

Well-known Member
Joined
May 15, 2007
Messages
823
Office Version
  1. 365
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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Upvote 0
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 :)
 
Upvote 0
Sorry about the confusion :)

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.
 
Upvote 0
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).
 
Upvote 0
What about the following:

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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top