Help Creating Macro [Closed]

Report
Posts
1
Registration date
Thursday January 23, 2014
Status
Member
Last seen
January 23, 2014
-
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
-
Hello,

I hope someone can help me since I don't have the knowledge to do it.

I'm trying to build a macro that check the value in an excel cell (A) and if it is between some values it changes another cell value to a specific value, and if the value in (A) is between some other values the other cell value will be changed to another value.

It will be used to a time schedule. Let me give an example:


| Day1__________________________________| Day 2
Agent# |start time|breakstart|breakend|End Time|start time|breakstart|breakend|End Time|
Agent1 -|.... 08:00 |.... 12:15 |......13:15|.....16:00|.....08:00 |......12:30 |.....12:30|......16:00|
Agent2 -|.... 09:30 |.....12:30 |......13:30|.......7:00|.....09:30 |......13:30 |.....14:30|......17:00|





If startime is between 08:00 and 09:00 then breakstart it will be replaced by 12:30 and breakend will be replaced by 13:30.
If startime is between 09:15 and 10:00 then breakstart will be replaced by 13:00 and breakend will be replaced by 14:00.



After running macro the result shoud be:

| Day1__________________________________| Day 2
Agent# |start time|breakstart|breakend|End Time|start time|breakstart|breakend|End Time|
Agent1 -|.... 08:00 |.... 12:30 |......13:30|.....16:00|.....08:00 |......12:30 |.....12:30|......16:00|
Agent2 -|.... 09:30 |.....13:00 |......13:30|.....17:00|.....09:30 |......13:00 |.....14:00|......17:00|


This is for as much columns as a month have and at least 100 agents (rows).

Thank you all.

3 replies

Anyone?
Help Please!
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Hi Joaopedr1,

Try this:
B3 = Start time
C3 = Break start
D3 = Break end

C3: =IF(AND(B3>=8/24,B3<=9/24),12.5/24,IF(AND(B3>=9.25/24,B3<=10/24),13/24,"Invalid Starttime"))

D3: =C3+1/24

Format your cells as U:MM.

Best regards,
Trowa