Probably something really simple...
 

I'm trying to create a calendar/organiser within Excel to schedule a
workload. To do this, i've set up a page which has a list of all the
jobs, the time its due to start and also how long it will take.

On another tab, I have a visual representation of this using coloured
cells. What I'm trying to do is loop through the job page, take the
time when a job is due to start and then find the time on the second
tab and simply put a '1' in the adjacent cell.

The problem that i have is that I cannot get Excel to recognize my data
as a time, therefore for 12:00 the variable shows 0.5 on the 2nd tab,
however on the 1st tab, the cell which shows 12:00 actually has the
figure of 0.5000000002 so it does not see it as a match.

My times are all broken down into 5 minutes (9:00 9:05 etc)

Any advice??

Thanks

Probably something really simple...
 

..5 is 12:00 Noon. Time is stored as the fraction of a 24 hour day. So .5 is
half a day or 12 hours.


think you need a better way to check equality. Here is one approach - if
the are within a minute of each other, call it equal.
if abs(cell1-cell2) < TimeSerial(0,1,0) then
' we have a match
End if

30 seconds would be

if abs(cell1-cell2) < timeSerial(0,0,30) Then

as an example of a tighter test.

Another test could be

if format(cell1,"hh:mm") = format(cell2,"hh:mm") then

--
Regards,
Tom Ogilvy


"DAV135" wrote:

I'm trying to create a calendar/organiser within Excel to schedule a
workload. To do this, i've set up a page which has a list of all the
jobs, the time its due to start and also how long it will take.

On another tab, I have a visual representation of this using coloured
cells. What I'm trying to do is loop through the job page, take the
time when a job is due to start and then find the time on the second
tab and simply put a '1' in the adjacent cell.

The problem that i have is that I cannot get Excel to recognize my data
as a time, therefore for 12:00 the variable shows 0.5 on the 2nd tab,
however on the 1st tab, the cell which shows 12:00 actually has the
figure of 0.5000000002 so it does not see it as a match.

My times are all broken down into 5 minutes (9:00 9:05 etc)

Any advice??

Thanks