Time Schedule Changing Every Week

[maeyks]

Hello,

Is there a way to return a time value every week and every other week? For example, this week, a person's schedule, Monday to Sunday will be 7 am to 4 pm and 8 am to 5 pm next week. Condition #2 is that a person's schedule, Monday to Sunday will be 7 am to 4 pm for two consecutive weeks and then 8 am to 5 pm afterwards.

Thank you


Mike

 

[RickXL]

Hi,

The short answer is: Yes.

But we will be needing some more details if you want some help.

For instance, are you looking for a formula to work on an existing worksheet?
Do you want a VBA solution?
Are you needing a user defined function?

How do you know what the schedules will be? Presumably, the data about hours per day is stored somewhere. Is that already in Excel or not?

 

[maeyks]

Hi

I am not yet able to do VBA programs, only excel. Its like I have a table of employees with a particular schedule. However each employee has a unique schedule. Employee 1's schedule changes weekly 7am and 8 am. While employee 2's schedule changes every two weeks, 7 am and 8 am

This may purely depend on the count of week, I think, and not on the date. Maybe week number?

Thanks.


Michael

 

[RickXL]

Hi,

Something like:
=MOD(WEEKNUM(TODAY()),2)
will give you the number 0 or 1 depending on the week number.

WEEKNUM returns a number between 1 and 53.
MOD works out the remainder if you divide a number by its second argument. For instance:
=MOD(23,3) would return 2. (23/3 = 7 remainder 2)

So you can use MOD to give you the week number of your cycle. NB it always starts at 0 so you may want to add 1.

 

[maeyks]

Hi,

Its like:
John Doe's schedule for
April 5 to 11 : 7 am to 4 pm
April 12 to 18 : 8 am to 5 pm
April 19 to 25 : 7 am to 4 pm
April 26 to May 2 : 8 am to 5 pm

while Jane Doe's schedule is

April 5 to 11 : 7 am to 4 pm
April 12 to 18 : 7 am to 4 pm
April 19 to 25 : 8 am to 5 pm
April 26 to May 2 : 8 am to 5 pm

How can I put that into excel formula?

Thank you.


Michael

Hi,

Something like:
=MOD(WEEKNUM(TODAY()),2)
will give you the number 0 or 1 depending on the week number.

WEEKNUM returns a number between 1 and 53.
MOD works out the remainder if you divide a number by its second argument. For instance:
=MOD(23,3) would return 2. (23/3 = 7 remainder 2)

So you can use MOD to give you the week number of your cycle. NB it always starts at 0 so you may want to add 1.

 

[RickXL]

Hi,

You have not said exactly how you need the output presenting. So I have left it as a string.

Here is one way:

Excel 2013
	A	B	C
1	Name	Date	Hours
2	John Doe	05/04/2015	7 am to 4 pm
3	John Doe	12/04/2015	8 am to 5 pm
4	John Doe	19/04/2015	7 am to 4 pm
5	John Doe	26/04/2015	8 am to 5 pm
6	Jane Doe	05/04/2015	7 am to 4 pm
7	Jane Doe	12/04/2015	7 am to 4 pm
8	Jane Doe	19/04/2015	8 am to 5 pm
9	Jane Doe	26/04/2015	8 am to 5 pm
Sheet1

Cell Formulas
Range	Formula
C2	=CHOOSE(MOD(WEEKNUM(B2),2)+1,"8 am to 5 pm","7 am to 4 pm")
C3	=CHOOSE(MOD(WEEKNUM(B3),2)+1,"8 am to 5 pm","7 am to 4 pm")
C4	=CHOOSE(MOD(WEEKNUM(B4),2)+1,"8 am to 5 pm","7 am to 4 pm")
C5	=CHOOSE(MOD(WEEKNUM(B5),2)+1,"8 am to 5 pm","7 am to 4 pm")
C6	=CHOOSE(MOD(WEEKNUM(B6),4)+1,"7 am to 4 pm","8 am to 5 pm","8 am to 5 pm","7 am to 4 pm","7 am to 4 pm")
C7	=CHOOSE(MOD(WEEKNUM(B7),4)+1,"7 am to 4 pm","8 am to 5 pm","8 am to 5 pm","7 am to 4 pm","7 am to 4 pm")
C8	=CHOOSE(MOD(WEEKNUM(B8),4)+1,"7 am to 4 pm","8 am to 5 pm","8 am to 5 pm","7 am to 4 pm","7 am to 4 pm")
C9	=CHOOSE(MOD(WEEKNUM(B9),4)+1,"7 am to 4 pm","8 am to 5 pm","8 am to 5 pm","7 am to 4 pm","7 am to 4 pm")