Date and Time Formula
 

Can anyone help with the following;

I have dates in column B e.g. 01/05/05 (could be lots of dates on the same
day or different days) and times in Column C e.g. 0900,1545 (all 24hour
clock) I want to be able to do a formula that says;

If the time in cell C1 is the first time after 0800 on the same date if it
appears in column B, then I want to see the result of 1 appear in D1.

e.g.

Column B Column C Column D
01/05/05 1045
01/05/05 0930 1
01/05/05 1517
02/05/05 1120 1
02/05/05 1640

Any help would be great!

Try this

=IF(B1=MIN(IF($A$1:$A$100=A1,$B$1:$B$100)),1,"")

which is an array formula, so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"Sho" wrote in message
...
Can anyone help with the following;

I have dates in column B e.g. 01/05/05 (could be lots of dates on the same
day or different days) and times in Column C e.g. 0900,1545 (all 24hour
clock) I want to be able to do a formula that says;

If the time in cell C1 is the first time after 0800 on the same date if it
appears in column B, then I want to see the result of 1 appear in D1.

e.g.

Column B Column C Column D
01/05/05 1045
01/05/05 0930 1
01/05/05 1517
02/05/05 1120 1
02/05/05 1640

Any help would be great!

Based on what you wrote, I think you intended to put a 1 in the first row.
Try something like this in D2 and fill down to the end:

=IF((B2=B1)*(TEXT(C2,"00\:00")*1="08:00"*1)*(SUMP RODUCT(($B$1:B2=B2)*(TEXT($C$1:C2,"00\:00")*1="08 :00"*1))=1),1,"")

Then use this in D1 in case the first time value is greater than 8:00:

=IF(TEXT(C1,"00\:00")*1="08:00"*1,1,"")

HTH
Jason
Atlanta, GA


"Sho" wrote:

Can anyone help with the following;

I have dates in column B e.g. 01/05/05 (could be lots of dates on the same
day or different days) and times in Column C e.g. 0900,1545 (all 24hour
clock) I want to be able to do a formula that says;

If the time in cell C1 is the first time after 0800 on the same date if it
appears in column B, then I want to see the result of 1 appear in D1.

e.g.

Column B Column C Column D
01/05/05 1045
01/05/05 0930 1
01/05/05 1517
02/05/05 1120 1
02/05/05 1640

Any help would be great!

On Thu, 19 May 2005 03:15:36 -0700, Sho wrote:

Can anyone help with the following;

I have dates in column B e.g. 01/05/05 (could be lots of dates on the same
day or different days) and times in Column C e.g. 0900,1545 (all 24hour
clock) I want to be able to do a formula that says;

If the time in cell C1 is the first time after 0800 on the same date if it
appears in column B, then I want to see the result of 1 appear in D1.

e.g.

Column B Column C Column D
01/05/05 1045
01/05/05 0930 1
01/05/05 1517
02/05/05 1120 1
02/05/05 1640

Any help would be great!

Assumptions:

1. Data begins in B2
2. Range B2:B100 is NAME'd Dates
3. Range C2:C100 is NAME'd Times

Use the **array-entered** formula in D2:

=IF(AND(ISNUMBER(C2),C2=MIN(IF(B2=Dates,Times,9999 ))),1,"")

and copy/drag it down as far as needed.

To **array-enter** a formula, after copying or typing it into C2, hold down
<ctrl + <shift while hitting <enter. Excel will place braces {...} around
the formula.

The ISNUMBER function is to ensure there is data in Column C or else it will
also give a blank result.


--ron

On Thu, 19 May 2005 07:07:24 -0400, Ron Rosenfeld
wrote:

I missed the part about the time having to be at or after 0800.

Change the formula to read:

=IF(AND(ISNUMBER(C2),C2=MIN(IF((B2=Dates)
*(Times=800),Times,9999))),1,"")

But still enter as an **array-formula**






Assumptions:

1. Data begins in B2
2. Range B2:B100 is NAME'd Dates
3. Range C2:C100 is NAME'd Times

Use the **array-entered** formula in D2:

=IF(AND(ISNUMBER(C2),C2=MIN(IF(B2=Dates,Times,999 9))),1,"")

and copy/drag it down as far as needed.

To **array-enter** a formula, after copying or typing it into C2, hold down
<ctrl + <shift while hitting <enter. Excel will place braces {...} around
the formula.

The ISNUMBER function is to ensure there is data in Column C or else it will
also give a blank result.


--ron

--ron

That is exactly what I wanted. What I do not understand is how it is
calculating it? Could you explain how the formula is calculating e.g. how
does IF($A$1:$A$100=A1,$B$1:$B$100) work out that if the date is the same it
finds the minimum time?


"Sho" wrote:

Can anyone help with the following;

I have dates in column B e.g. 01/05/05 (could be lots of dates on the same
day or different days) and times in Column C e.g. 0900,1545 (all 24hour
clock) I want to be able to do a formula that says;

If the time in cell C1 is the first time after 0800 on the same date if it
appears in column B, then I want to see the result of 1 appear in D1.

e.g.

Column B Column C Column D
01/05/05 1045
01/05/05 0930 1
01/05/05 1517
02/05/05 1120 1
02/05/05 1640

Any help would be great!

what this part does is test the whole range to be the date of the current
row, and returns only values where those dates match. This way, on a row
where the date is 01/01/2005, it builds an array of values associated with
that date, and ignores the values associted with other dates. MIN then works
on this array to extarct the MIN value for that date.

--
HTH

Bob Phillips

"Sho" wrote in message
...
That is exactly what I wanted. What I do not understand is how it is
calculating it? Could you explain how the formula is calculating e.g. how
does IF($A$1:$A$100=A1,$B$1:$B$100) work out that if the date is the same
it
finds the minimum time?


"Sho" wrote:

Can anyone help with the following;

I have dates in column B e.g. 01/05/05 (could be lots of dates on the
same
day or different days) and times in Column C e.g. 0900,1545 (all 24hour
clock) I want to be able to do a formula that says;

If the time in cell C1 is the first time after 0800 on the same date if
it
appears in column B, then I want to see the result of 1 appear in D1.

e.g.

Column B Column C Column D
01/05/05 1045
01/05/05 0930 1
01/05/05 1517
02/05/05 1120 1
02/05/05 1640

Any help would be great!