MID Function

[lehy2002]

HI! I am fairly familiar with excel but I am really having a problem with the calculations below. The Approval History is formatted as General, the Closed as Custom but no matter what I do with formatting it won't fix the error. One thing that did work is adding the text, even just the " - Group approval" part (no quotations) and it will work. I think I used to know this but I am stumped on why this is happening. Other question is, what is the purpose of the 2 hyphens in front of MID. (I think I know but I am not sure so I am asking.) Thank you so much and the quicker I can get an answer the better. I will be watching for responses.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Approval History[/TD]
[TD]Closed[/TD]
[TD]Function Approval Hx[/TD]
[TD]Function Closed[/TD]
[/TR]
[TR]
[TD]03/22/2018 08:49:02 AM - Group approval for Gatekeeper approved by user Angl Hoo (Group Approval).


[/TD]
[TD]3/23/2018 12:35:04 PM[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="align: right"]43181[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]IF(ISBLANK(E2),"",IF(ISERROR(--MID(E2,FIND("/",E2)-2,10)),--MID(E2,FIND("/",E2)-1,9),--MID(E2,FIND("/",E2)-2,10)))[/TD]
[TD]IF(ISBLANK(E2),"",IF(ISERROR(--MID(E2,FIND("/",E2)-2,10)),--MID(E2,FIND("/",E2)-1,9),--MID(E2,FIND("/",E2)-2,10)))[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

It would seem the value under 'Closed' is a real date value. Which is actually a numeric value, cell formatting makes it look like a date.
So the FIND function is not going to find a / character, resulting in the #Value ! Error.

Adding any text values to it makes it a TEXT string.

The -- converts a number stored as text to a real number.
The result of MID functions will not be a real number, just a text string.

 

[lehy2002]

How do I fix it? Changing it to Text didn't work.

 

[Jonmo1]

So your desired result is the 43181 right?

Try
=INT(E2)

 

[lehy2002]

Thank you - I think that is working. I will round up on the time column that I needed. For future though, how would I get rid of the times on the time/dates so instead of getting a time frame of like 2/19/2018 11:34:06 AM minus 2/15/2018 equals 3.65 and just 2/19/18 minus 2/15/18 I would just get 4 days? Thanks again! (The formatting doesn't change it. It might look like it but we both know it doesn't for real).

 

[Jonmo1]

The INT function will remove the time value.

DATES are the whole number, incrementing by 1 since Jan 1 1900
TIME is the decimal, 6AM = .25, NOON - .5, 6PM = .75 etc...

3.25 = Jan 3 1900 6AM


The INT function strips off the decimal(time) value.