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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
That's probably do-able.
Can you give more details about what exactly you want to do ?
 

B4L4KS

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

B4L4KS

Board Regular
Joined
Mar 7, 2011
Messages
69

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
13,597
Office Version
  1. 365
Platform
  1. Windows
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:

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115

ADVERTISEMENT

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

B4L4KS

Board Regular
Joined
Mar 7, 2011
Messages
69
thank you jasonb it works and week 1 and 2 come into it as there are two weeks to deal with,

kristian
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,597
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,734
Messages
5,708,166
Members
421,549
Latest member
Dtcfire

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
Top