Date and Time Formula

[Guest]

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!

 

[Bob Phillips]

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.

 

[Guest]

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)*(SUMPRODUCT(($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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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,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

--ron

 

[Guest]

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?

 

[Bob Phillips]

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.