EXCEL time match

[Bantham]

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.

 

[Claus Busch]

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

 

[joeu2004]

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".

 

[joeu2004]

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 .