Match Sum of Cells in multiple columns and ignore blanks

[ShelleyBelly]

Hello All,

I thought I'd nailed this but I think I'm well off the mark. I've two columns, one with dates and one with times, side by side, they'll be entered by humans and so keeping date and time separate is essential to avoid a **** up from the beginning. I then have a cell with a single cell with date and time combined. I require a match function to find the same of closest lesser date time to the date time in the single cell. Because there will be humans involved i also require it to ignore blank and only partially filled dates times. Oh and of course, the human will not have entered the dates or times in ascending of descending order.


Fri 20 Jan 2017 02:00 -> should return row 9, or number 9

Mon 23 Jan 2017 06:45
Tue 17 Jan 2017 06:45

Tue 24 Jan 2017 06:45
07:00
Fri 20 Jan 2017 06:45
Sat 21 Jan 2017 06:00

Thu 19 Jan 2017 07:00
Sat 21 Jan 2017
Wed 25 Jan 2017 06:45

Many thanks in advance,

Tom

 

[etaf]

you could use data validation

date is an integer count from 1/1/1900
and time is the decimal part

so you could in column A use data validation for whole value >0
and in column B use data validation for for decimal between 0 and 1