time problem or excel mistake

[engin]

hi

I ve a data table which consist columns time like that ( custom h:mm
format)

18:20 , 18:40 ,19:00 increasing every 20 minutes during 14 hours

in another sheet i d like pick up these datas according to time header

for example : if time is 19:00 i want to see the datas 20 minutes ago
datas with lookup formulas.

i used this formula : a1 -time (0,20,0)

it works but when time 21:00 and 03:00 and 06:00 it isnt working. its
very interesting . i checked and tried all formats but didnt work.

i d pleased if you help me

engin

 

[Peo Sjoblom]

What do you mean? You don't get 20:40 for 21:00 or 02:40 for 03:00?
Or what is the problem, with the LOOKUP?