-2

Having issues with this basic concept. I am making a gantt chart that displays agent coverage for the day. The issue I am having is I set the start time and the end time and their lunch and it ignores the lunch. I currently have this

Agent ------------START-------END----------LUNCH---------5:00 AM----5:30 AM-----------10:00 AM

Agent Name --- 5:00 AM ----1:30 PM-----10:00 AM---------TRUE-------TRUE--------------TRUE

=AND(START<=CURRENT,END>=NEXT_TIME,LUNCH<>CURRENT)

sin0cide
  • 65
  • 10
  • Was thinking if I could just find a way to compare the TIMEVALUE of the current time to a range which is defined by start and end it would work but can't find a way to do that. – sin0cide Jan 13 '15 at 00:58

1 Answers1

1

I was able to determine the issue by understanding the way excel stores time. I then used a second field that was formatted to general to compare my time values and found the issue I was having. I have three shifts (first,second,third) the first shift starts at 5am so I started the gantt chart there. I added 5:00 AM and 5:30 AM and then dragged the corner to go all the way to 5:00 AM again. Starting at that time the value is 0.208333333 which is fine but when I get to 12 PM it hits 0 and then the next 5 AM time is actually 1.208333333 because it went to the next day. So my basic comparison was having issues with the different numbers because my start and end were entered in manually instead of autofill. So the fix I came up with is to start with the top part of the gantt chart and make those cells a Named Range then when defining the Start,Lunch,End use Data Validation (List) to select the time and it perfectly matches and no need to add additional functions.

=AND(H$3>=$C4,I$3<=$F4,H$3<>$E4,$A4="Tier 2")

A good read to understand how time works in excel is http://www.cpearson.com/excel/datetime.htm

Community
  • 1
  • 1
sin0cide
  • 65
  • 10