Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement for Dates
Hello,
I have four dates that can out of order. For example, 04/10/08 then 03/10/08, then 05/10/08, then 06/10/08. I need an if statement to look at these four dates and determine if any of them are out of order and that tells me which date or dates are out of order. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement for Dates
This is at least the 4th time you have asked this same question. If you keep
asking the same question, you are going to keep getting the same responses. You have not responded with any of the 'troubleshooting' techniques that were asked of you from the 10/240/08 post of Formula for Dates. Until you troubleshoot your data, because there is something obviously wrong with it, you are going to continue to get the same answers that do not work for you. -- ** John C ** "Daren" wrote: Hello, I have four dates that can out of order. For example, 04/10/08 then 03/10/08, then 05/10/08, then 06/10/08. I need an if statement to look at these four dates and determine if any of them are out of order and that tells me which date or dates are out of order. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement for Dates
Put the four dates in A1 thru D1
In B2 enter: =IF(B1<A1,"value above is out of order","") then copy B2 to C2 thru D2 -- Gary''s Student - gsnu200810 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement for Dates
Pardon me, but this one is a little different. I need to highlight the
specific dates that are out of order, not just determine if they are out of order. "John C" wrote: This is at least the 4th time you have asked this same question. If you keep asking the same question, you are going to keep getting the same responses. You have not responded with any of the 'troubleshooting' techniques that were asked of you from the 10/240/08 post of Formula for Dates. Until you troubleshoot your data, because there is something obviously wrong with it, you are going to continue to get the same answers that do not work for you. -- ** John C ** "Daren" wrote: Hello, I have four dates that can out of order. For example, 04/10/08 then 03/10/08, then 05/10/08, then 06/10/08. I need an if statement to look at these four dates and determine if any of them are out of order and that tells me which date or dates are out of order. Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement for Dates
If you look at your 10/24/08 post, Mike gave a cell by cell check to check if
they are out of order. -- ** John C ** "Daren" wrote: Pardon me, but this one is a little different. I need to highlight the specific dates that are out of order, not just determine if they are out of order. "John C" wrote: This is at least the 4th time you have asked this same question. If you keep asking the same question, you are going to keep getting the same responses. You have not responded with any of the 'troubleshooting' techniques that were asked of you from the 10/240/08 post of Formula for Dates. Until you troubleshoot your data, because there is something obviously wrong with it, you are going to continue to get the same answers that do not work for you. -- ** John C ** "Daren" wrote: Hello, I have four dates that can out of order. For example, 04/10/08 then 03/10/08, then 05/10/08, then 06/10/08. I need an if statement to look at these four dates and determine if any of them are out of order and that tells me which date or dates are out of order. Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement for Dates
On Wed, 29 Oct 2008 06:12:01 -0700, Daren
wrote: Pardon me, but this one is a little different. I need to highlight the specific dates that are out of order, not just determine if they are out of order. That is not what you asked. You wrote: I need an if statement to look at these four dates and determine if any of them are out of order and that tells me which date or dates are out of order. There can be a difference between "highlight" and "tell me". You can highlight the appropriate cell by using conditional formatting. If your data is in A1:D1, select A1:D1 and use this formula in the Conditional Formatting wizard: =A1<SMALL($A$1:$D$1,COLUMN()) --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement for Dates
Assuming the dates are in adjacent cells in a column, say from A1 to A4, use
this formula in B1 and copy it down =A1=SMALL($A$1:$A$4,COUNT(A$1:A1)) "Daren" wrote: Hello, I have four dates that can out of order. For example, 04/10/08 then 03/10/08, then 05/10/08, then 06/10/08. I need an if statement to look at these four dates and determine if any of them are out of order and that tells me which date or dates are out of order. Thanks! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement for Dates
You're correct. I went back to that date and found
=and(a1<"",b1a1,c1b1,d1b1). That formula works, at least on one file. What does this formula do: =and(--d2--c2,--c2--b2,--b2--a2)? "John C" wrote: If you look at your 10/24/08 post, Mike gave a cell by cell check to check if they are out of order. -- ** John C ** "Daren" wrote: Pardon me, but this one is a little different. I need to highlight the specific dates that are out of order, not just determine if they are out of order. "John C" wrote: This is at least the 4th time you have asked this same question. If you keep asking the same question, you are going to keep getting the same responses. You have not responded with any of the 'troubleshooting' techniques that were asked of you from the 10/240/08 post of Formula for Dates. Until you troubleshoot your data, because there is something obviously wrong with it, you are going to continue to get the same answers that do not work for you. -- ** John C ** "Daren" wrote: Hello, I have four dates that can out of order. For example, 04/10/08 then 03/10/08, then 05/10/08, then 06/10/08. I need an if statement to look at these four dates and determine if any of them are out of order and that tells me which date or dates are out of order. Thanks! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement for Dates
The -- is a double unary. There are much better explanations in the groups
than I can give, just do a search on double unary. -- ** John C ** "Daren" wrote: You're correct. I went back to that date and found =and(a1<"",b1a1,c1b1,d1b1). That formula works, at least on one file. What does this formula do: =and(--d2--c2,--c2--b2,--b2--a2)? "John C" wrote: If you look at your 10/24/08 post, Mike gave a cell by cell check to check if they are out of order. -- ** John C ** "Daren" wrote: Pardon me, but this one is a little different. I need to highlight the specific dates that are out of order, not just determine if they are out of order. "John C" wrote: This is at least the 4th time you have asked this same question. If you keep asking the same question, you are going to keep getting the same responses. You have not responded with any of the 'troubleshooting' techniques that were asked of you from the 10/240/08 post of Formula for Dates. Until you troubleshoot your data, because there is something obviously wrong with it, you are going to continue to get the same answers that do not work for you. -- ** John C ** "Daren" wrote: Hello, I have four dates that can out of order. For example, 04/10/08 then 03/10/08, then 05/10/08, then 06/10/08. I need an if statement to look at these four dates and determine if any of them are out of order and that tells me which date or dates are out of order. Thanks! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement for Dates
Ron,
Selected the appropriate cells and tried your formula. The dates are in columns S thru V, with the date in T being out of order. The dates in S, T, U, and V respectively are 10/1/2007 8/11/2008 10/1/2007 4/1/2009. T is out of order. When I used =S11<SMALL($S$11:$V$11,COLUMN()) in the Formula Is of Conditional Formatting (not the wizard), and did conditional format as yellow, the highlight was not returned. The dates are formatted as 3/14/2001. Do you know what might be the problem? Thanks. "Ron Rosenfeld" wrote: On Wed, 29 Oct 2008 06:12:01 -0700, Daren wrote: Pardon me, but this one is a little different. I need to highlight the specific dates that are out of order, not just determine if they are out of order. That is not what you asked. You wrote: I need an if statement to look at these four dates and determine if any of them are out of order and that tells me which date or dates are out of order. There can be a difference between "highlight" and "tell me". You can highlight the appropriate cell by using conditional formatting. If your data is in A1:D1, select A1:D1 and use this formula in the Conditional Formatting wizard: =A1<SMALL($A$1:$D$1,COLUMN()) --ron |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement for Dates
On Wed, 29 Oct 2008 09:41:02 -0700, Daren
wrote: Ron, Selected the appropriate cells and tried your formula. The dates are in columns S thru V, with the date in T being out of order. The dates in S, T, U, and V respectively are 10/1/2007 8/11/2008 10/1/2007 4/1/2009. T is out of order. When I used =S11<SMALL($S$11:$V$11,COLUMN()) in the Formula Is of Conditional Formatting (not the wizard), and did conditional format as yellow, the highlight was not returned. The dates are formatted as 3/14/2001. Do you know what might be the problem? Thanks. The COLUMN() parameter is not evaluating to the correct number since you are not in COLUMN A. You need to adjust it so that when that function is executed in col S, it will return a 1. Look at HELP for the COLUMN() worksheet function and also for the SMALL worksheet function. Also look at the function I previously recommended to you when you were asking for a TRUE/FALSE response. An appropriate formula for conditional formatting, given your range of date entry, would be: =S1<SMALL($S$1:$V$1,COLUMN()-COLUMN($R$1)) --ron |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement for Dates
Ok, thanks, will try that
"Ron Rosenfeld" wrote: On Wed, 29 Oct 2008 09:41:02 -0700, Daren wrote: Ron, Selected the appropriate cells and tried your formula. The dates are in columns S thru V, with the date in T being out of order. The dates in S, T, U, and V respectively are 10/1/2007 8/11/2008 10/1/2007 4/1/2009. T is out of order. When I used =S11<SMALL($S$11:$V$11,COLUMN()) in the Formula Is of Conditional Formatting (not the wizard), and did conditional format as yellow, the highlight was not returned. The dates are formatted as 3/14/2001. Do you know what might be the problem? Thanks. The COLUMN() parameter is not evaluating to the correct number since you are not in COLUMN A. You need to adjust it so that when that function is executed in col S, it will return a 1. Look at HELP for the COLUMN() worksheet function and also for the SMALL worksheet function. Also look at the function I previously recommended to you when you were asking for a TRUE/FALSE response. An appropriate formula for conditional formatting, given your range of date entry, would be: =S1<SMALL($S$1:$V$1,COLUMN()-COLUMN($R$1)) --ron |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Statement for Dates
Thanks. That's a good approach, but I want to check all four dates against
each other using IF and then use conditional formatting and do this only in one cell. Can you assist further? "Gary''s Student" wrote: Put the four dates in A1 thru D1 In B2 enter: =IF(B1<A1,"value above is out of order","") then copy B2 to C2 thru D2 -- Gary''s Student - gsnu200810 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement Using Dates | Excel Discussion (Misc queries) | |||
If statement with dates | New Users to Excel | |||
Dates, Formats, and an IF statement! | Excel Discussion (Misc queries) | |||
IF statement with two dates | Excel Discussion (Misc queries) | |||
If Statement and Dates | Excel Worksheet Functions |