Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Min and Max Dates in Range that Contains Zeroes, Dates, and Number
I just can't figure this one out. In Row 7, have a range from Column C to
Column BE that contains cells containing numbers, cells containing dates, and cells containing text. There may be zeroes, the date equivalent of zero, or blank cells in each. Is there a formula that will return the value of the minimum date in the range, disregarding the "zeroes" in date cells and disregarding the cells containing numbers and text? Many thanks in advance for any suggestions! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Min and Max Dates in Range that Contains Zeroes, Dates, and Number
Try the below. Please note that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =MIN(IF(ISNUMBER(C7:BE7),IF(YEAR(C7:BE7)1900,C7:B E7))) If this post helps click Yes --------------- Jacob Skaria "Ezra" wrote: I just can't figure this one out. In Row 7, have a range from Column C to Column BE that contains cells containing numbers, cells containing dates, and cells containing text. There may be zeroes, the date equivalent of zero, or blank cells in each. Is there a formula that will return the value of the minimum date in the range, disregarding the "zeroes" in date cells and disregarding the cells containing numbers and text? Many thanks in advance for any suggestions! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Min and Max Dates in Range that Contains Zeroes, Dates, and Number
It depends on what the numbers are and what the dates are.
Since dates are really just numbers formatted to look like dates this can be a problem. If the numbers are within a certain range, say, 0 to a max of 100, and the dates are all greater than a certain date then you can try an array formula like this: =MAX(IF(C7:BE7100,C7:BE7)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Ezra" wrote in message ... I just can't figure this one out. In Row 7, have a range from Column C to Column BE that contains cells containing numbers, cells containing dates, and cells containing text. There may be zeroes, the date equivalent of zero, or blank cells in each. Is there a formula that will return the value of the minimum date in the range, disregarding the "zeroes" in date cells and disregarding the cells containing numbers and text? Many thanks in advance for any suggestions! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Min and Max Dates in Range that Contains Zeroes, Dates, and Nu
Jacob:
Worked like a charm! I've tested a few combos of entries in different cells, and so far, it seems to return the correct result each time. Thank you so much! I must admit that I always have trouble wrapping my brain around array formulas, and I doubt I would have come up with this EVER. Thanks! "Jacob Skaria" wrote: Try the below. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =MIN(IF(ISNUMBER(C7:BE7),IF(YEAR(C7:BE7)1900,C7:B E7))) If this post helps click Yes --------------- Jacob Skaria "Ezra" wrote: I just can't figure this one out. In Row 7, have a range from Column C to Column BE that contains cells containing numbers, cells containing dates, and cells containing text. There may be zeroes, the date equivalent of zero, or blank cells in each. Is there a formula that will return the value of the minimum date in the range, disregarding the "zeroes" in date cells and disregarding the cells containing numbers and text? Many thanks in advance for any suggestions! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Min and Max Dates in Range that Contains Zeroes, Dates, and Nu
So far, Jacob's idea just above seems to be working OK, but if I start
getting bogus results, I' ll keep this on tap, too. Thanks! "T. Valko" wrote: It depends on what the numbers are and what the dates are. Since dates are really just numbers formatted to look like dates this can be a problem. If the numbers are within a certain range, say, 0 to a max of 100, and the dates are all greater than a certain date then you can try an array formula like this: =MAX(IF(C7:BE7100,C7:BE7)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Ezra" wrote in message ... I just can't figure this one out. In Row 7, have a range from Column C to Column BE that contains cells containing numbers, cells containing dates, and cells containing text. There may be zeroes, the date equivalent of zero, or blank cells in each. Is there a formula that will return the value of the minimum date in the range, disregarding the "zeroes" in date cells and disregarding the cells containing numbers and text? Many thanks in advance for any suggestions! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Min and Max Dates in Range that Contains Zeroes, Dates, and Nu
Here's how it works...
In Excel dates are stored as the sequential count of days starting from a base date. That base date is 1/1/1900. The numeric value of 1/1/1900 is 1. 1/2/1900 = 2, 1/3/1900 = 3, 1/4/1900 = 4, etc. Today is 8/25/2009. The numeric value of 8/25/2009 is 40050. It's the 40,050th day since the base date of 1/1/1900. When you enter a date Excel (usually) automatically formats the cell to look like a date. For example, if you type in 8/25/2009 Excel *displays* that as the date 8/25/2009 but the true value of that cell is 40050. You can see this numeric value by entering any date in a cell then change the format of that cell to General. So, you can probably see how this makes differentiating dates from numbers kind of tricky. -- Biff Microsoft Excel MVP "Ezra" wrote in message ... So far, Jacob's idea just above seems to be working OK, but if I start getting bogus results, I' ll keep this on tap, too. Thanks! "T. Valko" wrote: It depends on what the numbers are and what the dates are. Since dates are really just numbers formatted to look like dates this can be a problem. If the numbers are within a certain range, say, 0 to a max of 100, and the dates are all greater than a certain date then you can try an array formula like this: =MAX(IF(C7:BE7100,C7:BE7)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Ezra" wrote in message ... I just can't figure this one out. In Row 7, have a range from Column C to Column BE that contains cells containing numbers, cells containing dates, and cells containing text. There may be zeroes, the date equivalent of zero, or blank cells in each. Is there a formula that will return the value of the minimum date in the range, disregarding the "zeroes" in date cells and disregarding the cells containing numbers and text? Many thanks in advance for any suggestions! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number of dates falling in a date range | Excel Worksheet Functions | |||
counting the number of dates in a date range | Excel Worksheet Functions | |||
Number of Saturdays within a range of dates | Excel Worksheet Functions | |||
Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
Identifying unique dates within a range of cells containing dates | Excel Discussion (Misc queries) |