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]

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

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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.

WO ID

Seq #

Qty

Qty Comp

Qty Avail

Start Date

Finish Date

Set Hrs

Run Hrs

Set

WO Due Date

Days Late

Days Aged

Prev Resource

Next Resource

Ops Left

Comments

Late/Ontime

OTD TO NEXT OP %

Sched Finish Date

OTD TO MPS

OTD TO MPS %

WO-109363/1.0

190

3

0

0

6/13/2016

6/13/2016

1

1

N

8/29/2016

14

27

M0002-CNC Lathe

S-SHOT

28

Late

0.00%

9/12/2016

Late

0.00%

<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>
</tbody>