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]

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.

Ok I understand. Thank you. It is showing that it is FALSE. I put the formula in (AD) and made (L3) into (X3) where the #VALUE! was showing.

 

[Jonmo1]

No, not the cell returning #Value!.
We want the ISNUMBER(??) to refer to the cell containing the Start Date


Can you post a small/desensitized copy of the file to a file sharing site like Box.com or something?

 

[MaureiraMat]

I dont have access to that. Do you have an e mail I would be able to send it to?

 

[Jonmo1]

Ok, those hours in N and O are NOT just whole numbers 1 hour or 2 hours.
You have decimals in there like 0.67

Try

=IF(L2+(N2+O2)/24 < M2+1,"On Time","Late ")

 

[MaureiraMat]

Ok, those hours in N and O are NOT just whole numbers 1 hour or 2 hours.
You have decimals in there like 0.67

Try

=IF(L2+(N2+O2)/24 < M2+1,"On Time","Late ")

Looks like the formatting did not transfer over. I will send you over an updated test book.

 

[Jonmo1]

ok, got the new file.
Where's the problem ?

 

[MaureiraMat]

ok, got the new file.
Where's the problem ?

I think there is no problem anymore. The formula you have given me look like it is producing the correct information.
I just was not sure if sending you the one in decimal format would change the outcome.

I just want to thank you so so much for taking the time to help me get through this. You have been an amazing help and I do very very much appreciate the time you spent helping me.

I hope you have a great rest of you're day!

Mat

 

[Jonmo1]

Glad to help, thanks for the feedback.