formula to switch between two weeks

B4L4KS

Board Regular
Joined
Mar 7, 2011
Messages
69
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
 
Upvote 0
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
thank you jasonb it works and week 1 and 2 come into it as there are two weeks to deal with,

kristian
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,222,312
Messages
6,165,275
Members
451,949
Latest member
bovacik

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top