Need If/Then Formula That Changes With Time

Hello,
Can anyone help me with the following problem? I need a (IF/Then) formula that would paste the same number into a column from 0:00 to 23:00 and change the next day at 0:00 and do the same thing until the next 0:00. The data to be change every 24 hours is in Sheet1 and the date and time is in Sheet2. I attempted to use a formula that only recognizes 0:00 but with 23 spaces between days the next cell pasted is not the 2nd day. Please see the attached spreadsheet and any all help is greatly appreciated. Thank You.

Can you format 2010.1.4 as a date?

Here, try this:

=VLOOKUP(DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)),Sheet1!$A$2:$C$14,3,FALSE)

as an alternative to the Date part of the formula

DATEVALUE(SUBSTITUTE(A2,".","/"))

less references!

Another way

Please Login or Register  to view this content.

I have put this and the option if you can change column A to dates formatted "yyyy.mm.dd"

Hope this helps