Excel date recognition issues

[MaureiraMat]

Hello,

I have a question on a chart that I am trying to build in excel. My goal is to find out if production on a machine is " on time to start date compared to the finish date, taking into consideration there is a " set up time and run time ".

Columns: L=start date , M=finish date , N= set up , O= run time

The problem I think I am running into is that when I have a start date of 6\6\16 and a finish date of 6\6\16 and my run time+set up time only = 5hrs for example, excel is thinking it is going to take 5 days ( this is what I think is going on). I don't think excel can tell this the set up time and run time is in hours and because of this it is showing everything as "late".


Here is the formula I am using: =IF(L40+N40+O40<M40,"On Time","Late")

If anyone could help me out i would greatly appreciate it!!

Thanks,

Mat

 

[MaureiraMat]

Hello,

I have a question on a chart that I am trying to build in excel. My goal is to find out if production on a machine is " on time to start date compared to the finish date, taking into consideration there is a " set up time and run time ".

Columns: L=start date , M=finish date , N= set up , O= run time

The problem I think I am running into is that when I have a start date of 6\6\16 and a finish date of 6\6\16 and my run time+set up time only = 5hrs for example, excel is thinking it is going to take 5 days ( this is what I think is going on). I don't think excel can tell this the set up time and run time is in hours and because of this it is showing everything as "late".


Here is the formula I am using: =IF(L40+N40+O40<m40,"on time","late")

If anyone could help me out i would greatly appreciate it!!

Thanks,

Mat

Looks like the formula did not paste. =IF(L40+N40+O40<M40,"On Time","Late")


</m40,"on>

 

[MaureiraMat]

=IF(L40+N40+O40 < M40, "On Time" , "Late " )

 

[Jonmo1]

Put spaces around any < or > symbols you have in the formula.
Forum software tends to treat them as HTML code tags, and formulas get cut off.

 

[bhos123]

seems like some formatting issue, Right click on cells with Time and then click on 'Format cells', choose Time formats

 

[MaureiraMat]

I tried that and it seems to be only changing the " time zone". It is not making excel understand that my run time and set up time are not in days but in hours.

 

[MaureiraMat]

[TABLE="width: 1739"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Seq #[/TD]
[TD]Qty[/TD]
[TD]Qty Comp[/TD]
[TD]Qty Avail[/TD]
[TD]Start Date[/TD]
[TD]Finish Date[/TD]
[TD]Set Hrs[/TD]
[TD]Run Hrs[/TD]
[TD]Set[/TD]
[TD]WO Due Date[/TD]
[TD]Days Late[/TD]
[TD]Days Aged[/TD]
[TD]Prev Resource[/TD]
[TD]Next Resource[/TD]
[TD]Ops Left[/TD]
[TD]Comments[/TD]
[TD]Late/Ontime[/TD]
[TD]OTD TO NEXT OP %[/TD]
[TD]Sched Finish Date[/TD]
[TD]OTD TO MPS[/TD]
[TD]OTD TO MPS %[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]6/22/2016[/TD]
[TD]6/22/2016[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD]6/15/2016[/TD]
[TD]37[/TD]
[TD]7[/TD]
[TD]S-LPI[/TD]
[TD]S-LPI[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]Late[/TD]
[TD]0.00%[/TD]
[TD]7/22/2016[/TD]
[TD]Late[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]6/10/2016[/TD]
[TD]6/10/2016[/TD]
[TD]1[/TD]
[TD]13[/TD]
[TD]N[/TD]
[TD]8/8/2016[/TD]
[TD]-18[/TD]
[TD]70[/TD]
[TD]RECV INSP[/TD]
[TD]DEBURR[/TD]
[TD]13[/TD]
[TD][/TD]
[TD]Late[/TD]
[TD]0.00%[/TD]
[TD]7/21/2016[/TD]
[TD]Late[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]6/15/2016[/TD]
[TD]6/16/2016[/TD]
[TD]0[/TD]
[TD]13[/TD]
[TD]N[/TD]
[TD]8/8/2016[/TD]
[TD]-18[/TD]
[TD]70[/TD]
[TD]INT-100[/TD]
[TD]INSP[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]Late[/TD]
[TD]0.00%[/TD]
[TD]7/21/2016[/TD]
[TD]Late[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]6/13/2016[/TD]
[TD]6/14/2016[/TD]
[TD]0[/TD]
[TD]13[/TD]
[TD]N[/TD]
[TD]8/8/2016[/TD]
[TD]-18[/TD]
[TD]70[/TD]
[TD]INT-100[/TD]
[TD]IDENT[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]Late[/TD]
[TD]0.00%[/TD]
[TD]7/21/2016[/TD]
[TD]Late[/TD]
[TD]0.00%[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

If N40 and O40 are whole numbers representing hours, then they are considered DAYS by Excel's Date/Time logic.

Try

=IF(L40+TIME(N40,0,0)+TIME(O40,0,0) < M40, "On Time" , "Late " )

 

[MaureiraMat]

It looks like this formula has changed many of the "late's" to "on times". If you could just give me an explanation of why this has changed them so I can better understand I would appreciate it. Has this formula made excel know that "n" and "o" are now in hours and not days?

 

[MaureiraMat]

I still don't think that excel is counting the run time and set up time as hours. A job that starts on 6/13/16 and finishes on 6/13/16 with run and set up hours equaling 2 total is still showing as late even though there is 24 hours between the time it has to start and finish.

[TABLE="width: 1891"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]WO ID[/TD]
[TD]Seq #[/TD]
[TD]Qty[/TD]
[TD]Qty Comp[/TD]
[TD]Qty Avail[/TD]
[TD]Start Date[/TD]
[TD]Finish Date[/TD]
[TD]Set Hrs[/TD]
[TD]Run Hrs[/TD]
[TD]Set[/TD]
[TD]WO Due Date[/TD]
[TD]Days Late[/TD]
[TD]Days Aged[/TD]
[TD]Prev Resource[/TD]
[TD]Next Resource[/TD]
[TD]Ops Left[/TD]
[TD]Comments[/TD]
[TD]Late/Ontime[/TD]
[TD]OTD TO NEXT OP %[/TD]
[TD]Sched Finish Date[/TD]
[TD]OTD TO MPS[/TD]
[TD]OTD TO MPS %[/TD]
[/TR]
[TR]
[TD]WO-109363/1.0[/TD]
[TD]190[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]6/13/2016[/TD]
[TD]6/13/2016[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD]8/29/2016[/TD]
[TD]14[/TD]
[TD]27[/TD]
[TD]M0002-CNC Lathe[/TD]
[TD]S-SHOT[/TD]
[TD]28[/TD]
[TD][/TD]
[TD]Late [/TD]
[TD]0.00%[/TD]
[TD]9/12/2016[/TD]
[TD]Late[/TD]
[TD]0.00%

[/TD]
[/TR]
</tbody>[/TABLE]