Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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! |
#7
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically enter date and time but only update once. | New Users to Excel | |||
Help - Information with time and date | Excel Discussion (Misc queries) | |||
Need help with date & time formula | Excel Worksheet Functions | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
time and date formula | Excel Worksheet Functions |