Extracted time from date/time field does not flag on =IF(TIME function.

**Vlad717** · 02-09-2013, 02:43 PM

Hi Everyone,

I have a sheet that I take daily reports from a server, paste the raw data in and it extracts it and posts the information in all the relevant fields. It is not quite working perfectly yet.

The date and time come in as ... This cannot be changed so I am stuck with it.

Please Login or Register  to view this content.

I can get it extracted to and display the time of...

Please Login or Register  to view this content.

The next step that I need is that it has to flag 1 (for yes) or 0 (for no) if the time falls between 19:00 to 07:30. Basically night time. I am using the following functions for this.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Then finishes off with flagging yes or no if either of the above are 1.

Please Login or Register  to view this content.

The problem now, is that the =IF(AND(E5 part does not actually detect this as being time. If I manually write in the time it works perfectly fine. Any suggestions on how to fix this? I've attached a basic sheet that shows this in action in a few steps which might help get a better understanding of this.

Thanks,

Example problem of time.xlsx

**Jakobshavn** · 02-09-2013, 03:08 PM

This only addresses your first issue (getting the time). With your data in A1, in B1 enter:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) which will display

9:29PM

Then in C1, enter:

=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B1,"PM"," PM"),"AM"," AM")) which will display:

9:29:00 PM

Once properly formatted!

This is a true "time" that can be used in your IF formulas

**daddylonglegs** · 02-09-2013, 03:08 PM

Try changing D5 formula by adding +0, that will convert to a valid time value

=SUBSTITUTE(SUBSTITUTE(C5,"P"," P"),"A"," A")+0

Now format D5 as hh:mm

Now check whether that time is between 19:00 and 07:30 with this formula

=OR(D5>="19:00"+0,D5<="7:30"+0)

**Vlad717** · 02-09-2013, 03:29 PM

Thank you for the quick responses guys. I tried both and they both work. I am going to go with daddylonglegs's formula on this one as it cuts out an additional two columns of my formulas to get the same result.

Thank you again.