Index-match only works after I click the cell to match and press enter

[J01234]

Hi all, I have two separate problems. The main goal is to make a list of half-hourly date-times (column B), and then match a bill invoice to these dates.

1) Problem 1:

I typed two date-times and then autofilled below them to keep half-hourly increments. However, they don't stay half-hourly, why? [see Picture 1]

For example, I started typing B2 = "22/12/2015 14:00:00" and B3 ="22/12/2015 14:30:00". I selected both cells and then when autofilling, B4 becomes "22/12/2015 15:00:00" as expected. The problem is at B102 which becomes "24/12/2015 15:59" instead of "24/12/2015 14:00:00"

2) See attached Picture 2 for screenshots and a description of what I did.

But basically, instead of using autofill, I typed B2 = "22/12/2015 14:00:00" and then added half-hourly increments e.g B3 = "B2+"00:30:00"". This didn't have the same problem as above. I then tried to match this to my bill invoice but it returned N/A. To fix the N/A, I had to select my date (e.g. cell B2) and click the formula, then press Enter. Suddenly the match worked. I don't want to manually select all my column B dates and press Enter - how can I speed this up? My calculation option is set to automatic already fyi.

Thanks.
J

Picture 1:

Picture 2:

 

[steve the fish]

Sounds like text problem to me. Does this work:

=MATCH(TEXT(F2,"dd/mm/yyyy hh:mm"),K:K,0)

If so highlight the column and then go to data tab, press text to columns and press finish.

 

[J01234]

Thanks for the suggestion

I actually made it work by replacing all the / with / in column F

The first problem I had is still a bit of a puzzle though!

 

[steve the fish]

Thats just doing the same thing. You are coercing your textual dates to become true dates. Match wont see a textual date as the same as a true date so wont find a match.