How do you code time?

[zilch4ry]

hi,

I have created a table which has information placed between the different sections which document 4 hour gaps (06:00-10:00,10:00-14:00, 14:00:18:00 and 18:00 - 22:00). I want to show on a different worksheet, if the =now() formula shows that the time is between say 14:00 and 18:00 then for the figure next to the time to show, however, I have only written the times in text, so it doesn't actually register the time gap.

I have created a smaller table which is a little example to work with :P

the figures on the right (in green) automatically change

If anybody could help me with this it would be much appreciated

Thanks

 

[Aladin Akyurek]

Images are not very useful for the contents cannot be loaded into Excel cells. That said:

Why not use 2 cells to represent a time interval? Example:

A2: 06:00
B2: 10:00

etc.

 

[zilch4ry]

Thank you for the answer, but i'm not the best at this :P- is there not a way to just tell excel that if the =now() shows the time "15:40" then you should take the information which is next to the cell containing the time gap "14:00 -18:00"?

 

[Aladin Akyurek]

Thank you for the answer, but i'm not the best at this :P- is there not a way to just tell excel that if the =now() shows the time "15:40" then you should take the information which is next to the cell containing the time gap "14:00 -18:00"?

Try...

A2: 06:00
A3: 10:00
A4: 14:00
A5: 18:00

B2: 06:00 - 10:00
B3: 10:00 - 14:00
B4: 14:00 - 18:00
B5: 18:00 - 22:00

C2: 6
C3: 8
C4: 8
C5: 4

Now try...

=LOOKUP(MOD(NOW(),1),$A$2:$A$5,$C$2:$C$5)

 

[zilch4ry]

I haven't a clue what that formula does, but thank you so much for the help- it worked great

 

[Aladin Akyurek]

I haven't a clue what that formula does, but thank you so much for the help- it worked great

The MOD bit picks out the time part from NOW().

LOOKUP matches that time part against $A$2:$A$5, which is in ascending order, and returns the corresponding value from $B$2:$B$5.

If of any interest, the following do the same:

=VLOOKUP(MOD(NOW(),1),$A$2:$C$5,3,1)

=INDEX($C$2:$C$5,MATCH(MOD(NOW(),1),$A$2:$A$5,1))