Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL time match
Hi,
I have an excel workbook into which I have the current time in cell A1, i then have columns with user efined time, starting in d4 1ith 00:00:00, i then use the formula =d4+1/1440*45 to get increments of 45 mins, i have another column with the formula =K4+1/1440*50, so i get 50 minute increments. What i need to be able to do is match the current time to these increments when it come to that time, eg, if increment time is 13:45, and current time has just hit 13:45 then I need to be able to identify this in an =if(current time = increment time),1,0) . I know ther is a difference in the time because the seconds keep changing and the floating point issue, is there a way that I can achieve this ? I am not very proficient in excel so any answers with as much help as possible would be greatly appreciated. Many thanks for all your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL time match
Hi,
Am Fri, 6 May 2011 08:08:08 -0700 (PDT) schrieb Bantham: I know ther is a difference in the time because the seconds keep changing and the floating point issue, is there a way that I can achieve this ? in D5 try: =D4+TIME(,45,) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL time match
On May 6, 8:08*am, Bantham wrote:
starting in d4 1ith 00:00:00, i then use the formula =d4+1/1440*45 to get increments of 45 mins, i have another column with the formula =K4+1/1440*50, so i get 50 minute increments. [....] I need to be able to identify this in an =if(current time = increment time),1,0) . There are two separate issues: 1. Separating the date from "current time", which I assume is NOW(). 2. Comparing time to the minute, ignoring differences in seconds. For "current time", you might write MOD(NOW(),1). It is better to put =MOD(NOW(),1) into a cell A1, at least for testing purposes. To compare to the minute, there are several ways. One way: =IF(TEXT(A1,"hh:mm")=TEXT(D5,"hh:mm"),1,0) The following is more efficient. But it might be inaccurate due to floating-point issues: =IF(INT(1440*A1)=INT(1440*D5),1,0) For example, consider when "current time" (A1) is =MOD(TODAY() +"00:50:00",1), and D5 is 00:50:00 (or =50/1440). The TEXT comparison is TRUE, as expected. But the INT comparison is FALSE. You can see why by putting =1440*A1 and =1440*D5 into cells formatted as Number with 14 decimal places. Note that 1440*A1 is significantly less than 50. The reason is that MOD(NOW(),1) truncates significant bits on the right compared to 50/1440 because NOW() must use some bits to represent TODAY(), the integer part. Ostensibly, a fix is to write: =IF(ROUND(1440*A1,0)=ROUND(1440*D5,0),1,0) But that will round seconds, which might not be what you want. For example, if "current time" (A1) is =MOD(TODAY()+"00:49:31",1) and D5 is 00:50:00. The ROUND comparison is TRUE even though they are not equal to the minute. PS: Generally, it is better to write =--TEXT(K4+1/1440*50,"hh:mm") formatted as Custom hh:mm. This should ensure that the binary representation is the same as the equivalent constant. The double- negative converts text to numeric. And of course, it would be better to write K4+50/1440, K4+TIME(0,50,0) or K4+"00:50:00". |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EXCEL time match
On May 6, 9:45*am, I wrote:
For "current time", you might write MOD(NOW(),1). *It is better to put =MOD(NOW(),1) into a cell A1, at least for testing purposes. To compare to the minute, there are several ways. *One way: =IF(TEXT(A1,"hh:mm")=TEXT(D5,"hh:mm"),1,0) Alternatively, put =--TEXT(NOW(),"hh:mm") into A1. Then the following __should__ work: =IF(A1=D5,1,0) However, note that IF(A1-D5=0,1,0) might not work in some cases due to floating-point issues. The simpler form, IF(A1=D5,...), works because of the dubious heuristic poorly described under the misleading title "Example When a Value Reaches Zero" at http://support.microsoft.com/kb/78113 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
match function - date and time | Excel Discussion (Misc queries) | |||
Match help again easier this time | Excel Discussion (Misc queries) | |||
Worksheet function match - run time error | Excel Worksheet Functions | |||
Match doesn't work all the time? | Excel Discussion (Misc queries) | |||
one more time index/match | Excel Discussion (Misc queries) |