Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
Make it more simple or intuitive to do simple things | Charts and Charting in Excel | |||
simple question, hopefully a simple answer! | Excel Programming |