Dealing with Times

Copper Contributor

Hello everyone

 

I am dealing with this formula =AND(AA9>=AD9, AA9<=Z9).  I have a time and I want to see if it is within 2 other times.  The part that is driving me nuts is sometimes this is returning a True statement when I know it is not.

 

Currently the times are 15:30:00 and 17:00:00 and excel is saying 12:00:00 is true statement and found between those times.  I must be doing something wrong in the format or something.  Please help.

 

Thanks

Jaime

11 Replies

@jperezmarkivonlineco 

What happens if you format AA9, AD9 and Z9 as General? Which values do you see?

@Hans Vogelaar 

 

Hello. That is how I started then I changed the format to H:MM:SS.

@jperezmarkivonlineco 

In general it shall work

image.png

but it could return TRUE if some of them are texts

image.png

@Hans Vogelaar 

 

Nothing changed.  :(

@Sergei Baklan 

 

Hello. I have been trying >= and <=.  I wonder if the = throws it off.

@jperezmarkivonlineco 

If nothing changed, that suggests that the values are not real time values, but text values.

Do the following:

  • Select one of the cells.
  • Press F2, then press Enter.
  • If the value changes to a time, repeat for the other two cells.
  • Otherwise, make sure that there are no spaces before or after the text.

@jperezmarkivonlineco 

Could you please provide sample file without sensitive information?

@Hans Vogelaar 

 

That helps.  It is a format issue.  I had just took every column and made them plus 0 which made the formats the same and it works.  Very similar to what you said to do.  I just don't know how to do what you said in bulk instead of one by one.  Thank you so much for the help.

@jperezmarkivonlineco 

You can do the following:

 

  • Select an empty cell
  • Copy it to the clipboard.
  • Select all the cells with times.
  • Right-click in the selection and select 'Paste Special...' from the context menu.
  • Or click the lower half of the Paste button on the Home tab of the ribbon and select 'Paste Special...'
  • Select Add.
  • Click OK.

@Sergei Baklan 

 

Thank you for your time.  It was a formatting issue.  This might not be the best way to correct it but I took the columns in questions and did =column+0 and that gets them in the same format.  Then the formula worked!!!

@jperezmarkivonlineco 

That means values are texts. =(text which presents number/time)+0 returns that number/time.

Only format applying is not enough for such conversion, you shall re-enter value or apply arithmetic as above, after use desired format if necessary.