formula to switch between two weeks

[B4L4KS]

hi

i need to create a formula that selects a cell, eg 1 and can select another cell, eg 2

i then have to type in week 1 and week 2 that will select the data from that cell.

week 1 and 2 will have to automatically change each week.

thanks in advance

kristian

 

[Gerald Higgins]

That's probably do-able.
Can you give more details about what exactly you want to do ?

 

[B4L4KS]

hi

sorry for the delay

this is the data for all the cells,

this has become more complicated as a reference to all the cells between the particular times will have to be shown, so Monday between 6-10 will have to have the number 6 ect.

any help will be appreciated thanks

kristian

 

[Gerald Higgins]

Sorry, I'm not at all clear what you actually want to do.

 

[B4L4KS]

hi
i need to create a formula using the data so between the times and the days the correct number will appear,

e.g. 6-10 on Monday will equal 6 and so on but to the exact date and time that is present.

thanks

kristian

 

[jasonb75]

Based on your table in post #3, try

=INDEX($F$46:$J$52,WEEKDAY(TODAY(),2),MATCH(HOUR(NOW()),{6,10,14,18}))

Note that you will need to unmerge columns F & G and remove the empty cells in G or this will fail.

although I fail to see how that table and the information you have in post #5 relate to the original question. Where does week 1 / week 2 come into it?

 

[Gerald Higgins]

OK. Why does "Monday 6-10" equal 6 and not, say, 4, or 8, or 257.31 ?

 

[B4L4KS]

thank you jasonb it works and week 1 and 2 come into it as there are two weeks to deal with,

kristian

 

[jasonb75]

Your original question suggested that you wanted a formula to determine the week number from the current date.

Based on week starting on monday, if today is week 1 try something like

=1+ISEVEN((TODAY()-2)/7)

if today is week 2,

=1+ISODD((TODAY()-2)/7)

This is the simplest version, but may cause incorrect results if you use the 1904 date system.