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

 

[Jonmo1]

OK, I see what happened there.
The TIME function doesn't do elapsed time (anything over 24 hours, circles back to 0 hours)

Try
=IF(L2+(N2+O2&":00:00")<M2+1,"On Time","Late ")

 

[MaureiraMat]

excel is telling me that the formula is missing a parenthesis or comma? i am not sure where to put it

 

[Jonmo1]

Fell victim on my own to the Forum Software cutting off formulas at < or > symbols..lol

Should be

=IF(L2+(N2+O2&":00:00") < M2+1,"On Time","Late ")

 

[MaureiraMat]

Lol happens to the best of us.

Looks like we are getting closer.. I am getting some #VALUE! in my (X) column. Do you know why this might be?

[TABLE="width: 1793"]
<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]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]
[/TR]
[TR]
[TD]WO-101343/8.0[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6/7/2016[/TD]
[TD="align: right"]6/7/2016[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]0[/TD]
[TD]Y[/TD]
[TD="align: right"]5/10/2016[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]28[/TD]
[TD]ASSY[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Late [/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]6/7/2016[/TD]
[TD]Late[/TD]
[/TR]
[TR]
[TD]WO-11900/54Z.0[/TD]
[TD="align: right"]265[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6/20/2016[/TD]
[TD="align: right"]6/20/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]N[/TD]
[TD="align: right"]7/6/2016[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]21[/TD]
[TD]RECV INSP[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: center"]#VALUE![/TD]
[TD="align: right"]6/29/2016[/TD]
[TD]Late[/TD]
[/TR]
[TR]
[TD]WO-11900/54Z.0[/TD]
[TD="align: right"]230[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6/8/2016[/TD]
[TD="align: right"]6/8/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]N[/TD]
[TD="align: right"]7/6/2016[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]21[/TD]
[TD]INSP[/TD]
[TD]SHIPPING[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]On Time[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]6/29/2016[/TD]
[TD]Late[/TD]
[/TR]
[TR]
[TD]WO-11900/54Z.0[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6/9/2016[/TD]
[TD="align: right"]6/15/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]N[/TD]
[TD="align: right"]7/6/2016[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]21[/TD]
[TD]S-MPI[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]On Time[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]6/29/2016[/TD]
[TD]Late[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

That indicates the value in the Start Date column (L) isn't a real date. Just a TEXT string that looks like a date.
Verify with =ISNUMBER(L3)
Where L3 is the Start Date on the row giving #Value! Error

 

[Jonmo1]

Or the Hours in N or O may also be TEXT. Test those with ISNUMBER as well.

 

[MaureiraMat]

That indicates the value in the Start Date column (L) isn't a real date. Just a TEXT string that looks like a date.
Verify with =ISNUMBER(L3)
Where L3 is the Start Date on the row giving #Value! Error

Sorry I am not understanding where I must add that formula?

 

[Jonmo1]

Sorry I am not understanding where I must add that formula?

Anywhere you want, any available cell.
It's just a troubleshooting test, not solution.

Verifying if your dates are really dates, or just text strings.

 

[MaureiraMat]

Anywhere you want, any available cell.
It's just a troubleshooting test, not solution.

Verifying if your dates are really dates, or just text strings.

I put the formula in to cell AD as it is empty, I am getting an answer of TRUE. Am I doing this right? I have not done this before. Sorry about this.

 

[Jonmo1]

The idea here is to test if the Start Date is a real date.
The formula we want to use (in any cell you like) is

=ISNUMBER(L3)

Make sure to adjust the L3 to reflect the cell holding the Start Date in one of the rows that is returning #Value! for the formula we've been working on.