Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how can i find a specific date of a database
so dear friends i am working in an NGOs here in Afghanistan
and here i have my data about populations and those whos age is less then 31-dec-1979 are called underage and the rest of called Adult so how can find the correct result , my date in age Column in Excel sheet is below Column c 1-jan-1980 23-jul-1985 3-apr-2002 1980 3-nov-1996 1958 so i have such kind of data . so plz i really need ur help. that must be appreciated. thanks alot Looking forward for Solution |
#2
|
|||
|
|||
Hello
Ok. Quite simple I think. The 'serial' date for 01-Dec-1979 in Excel is 29220. Excel uses serial numbers to store dates. All you need to know is that 01-Dec-1979 = 29220. As I undertand it you have a list of dates in column C and want to assess whther they are 'UNDERAGE' or 'ADULT' using 01-Dec-1979 as the cut-off. Column C Column D 1-Jan-1980 =formula 23-Jul-1985 03-Apr-2002 1980 03-Nov-1996 1958 In column D1 (where I have put = formula), type in the formula as shown below and then drag down to calculate for all cells. =IF(LEN(B46)4,IF(B4629220,"UNDERAGE","ADULT"),IF (B46=1980,"UNDERAGE","ADULT")) [NB - the formula is all one line - it appears as two lines here due to space constaints) This will take care of dates whether they are of the format dd-mm-yyyy or just simply yyyy. I hope this helps. If there are any problems then please write back and I shall endeavour to assist you further. Regards Alex "reneabesmer" wrote: so dear friends i am working in an NGOs here in Afghanistan and here i have my data about populations and those whos age is less then 31-dec-1979 are called underage and the rest of called Adult so how can find the correct result , my date in age Column in Excel sheet is below Column c 1-jan-1980 23-jul-1985 3-apr-2002 1980 3-nov-1996 1958 so i have such kind of data . so plz i really need ur help. that must be appreciated. thanks alot Looking forward for Solution |
#3
|
|||
|
|||
Alex,
I would never recommend using the serial date, it is error prone, and it just makes it far too difficult to understand. For instance, I make 1st Dec 1979 is 29190 :-). There are better ways, such as DATE(1979,12,01), or, my preference, --"1979-12-01". The other thing I would do is make some assumption about years, such as first day of year, so I would end up with =IF(IF(B11=4,DATE(B11,1,1),B11)--"1979-12-01","UNDERAGE","ADULT") -- HTH RP (remove nothere from the email address if mailing direct) "Alex" wrote in message ... Hello Ok. Quite simple I think. The 'serial' date for 01-Dec-1979 in Excel is 29220. Excel uses serial numbers to store dates. All you need to know is that 01-Dec-1979 = 29220. As I undertand it you have a list of dates in column C and want to assess whther they are 'UNDERAGE' or 'ADULT' using 01-Dec-1979 as the cut-off. Column C Column D 1-Jan-1980 =formula 23-Jul-1985 03-Apr-2002 1980 03-Nov-1996 1958 In column D1 (where I have put = formula), type in the formula as shown below and then drag down to calculate for all cells. =IF(LEN(B46)4,IF(B4629220,"UNDERAGE","ADULT"),IF (B46=1980,"UNDERAGE","ADU LT")) [NB - the formula is all one line - it appears as two lines here due to space constaints) This will take care of dates whether they are of the format dd-mm-yyyy or just simply yyyy. I hope this helps. If there are any problems then please write back and I shall endeavour to assist you further. Regards Alex "reneabesmer" wrote: so dear friends i am working in an NGOs here in Afghanistan and here i have my data about populations and those whos age is less then 31-dec-1979 are called underage and the rest of called Adult so how can find the correct result , my date in age Column in Excel sheet is below Column c 1-jan-1980 23-jul-1985 3-apr-2002 1980 3-nov-1996 1958 so i have such kind of data . so plz i really need ur help. that must be appreciated. thanks alot Looking forward for Solution |
#4
|
|||
|
|||
Bob
Thanks for your note. On your points... (1) I wasn't aware that there were errors in the serial date function. Thanks for drawing that to my attention. (2)Your methodology concerning making assumptions about the year is good. It offers greater precision and you offer a more robust solution. Hopefully the original sender of the mesage is now equipped to carry out their task at hand. Regards Alex "Bob Phillips" wrote: Alex, I would never recommend using the serial date, it is error prone, and it just makes it far too difficult to understand. For instance, I make 1st Dec 1979 is 29190 :-). There are better ways, such as DATE(1979,12,01), or, my preference, --"1979-12-01". The other thing I would do is make some assumption about years, such as first day of year, so I would end up with =IF(IF(B11=4,DATE(B11,1,1),B11)--"1979-12-01","UNDERAGE","ADULT") -- HTH RP (remove nothere from the email address if mailing direct) "Alex" wrote in message ... Hello Ok. Quite simple I think. The 'serial' date for 01-Dec-1979 in Excel is 29220. Excel uses serial numbers to store dates. All you need to know is that 01-Dec-1979 = 29220. As I undertand it you have a list of dates in column C and want to assess whther they are 'UNDERAGE' or 'ADULT' using 01-Dec-1979 as the cut-off. Column C Column D 1-Jan-1980 =formula 23-Jul-1985 03-Apr-2002 1980 03-Nov-1996 1958 In column D1 (where I have put = formula), type in the formula as shown below and then drag down to calculate for all cells. =IF(LEN(B46)4,IF(B4629220,"UNDERAGE","ADULT"),IF (B46=1980,"UNDERAGE","ADU LT")) [NB - the formula is all one line - it appears as two lines here due to space constaints) This will take care of dates whether they are of the format dd-mm-yyyy or just simply yyyy. I hope this helps. If there are any problems then please write back and I shall endeavour to assist you further. Regards Alex "reneabesmer" wrote: so dear friends i am working in an NGOs here in Afghanistan and here i have my data about populations and those whos age is less then 31-dec-1979 are called underage and the rest of called Adult so how can find the correct result , my date in age Column in Excel sheet is below Column c 1-jan-1980 23-jul-1985 3-apr-2002 1980 3-nov-1996 1958 so i have such kind of data . so plz i really need ur help. that must be appreciated. thanks alot Looking forward for Solution |
#5
|
|||
|
|||
"Alex" wrote in message ... Bob Thanks for your note. On your points... (1) I wasn't aware that there were errors in the serial date function. Thanks for drawing that to my attention. Sorry, it appears I was not clear here. I am not saying there are errors in the serial date function, just that it is much easier to make a mistake in giving a serial date rather than the date date. For example, you said the serial date for Dec 1st 1979 is 29220. I believe you will find that it is actually 29190, you gave the serial date for 30th Dec 1979, making exactly the mistake that I believe is so easy to make :-). Not seeing it as a date makes this all to easy. Regards Bob |
#6
|
|||
|
|||
Bob
Ok. Point taken - I see what you mean. In truth, I haven't ever used the =DATE() function. Using the serial number was what immediately sprang to mind...and I think I proved why you should try and avoid doing that as you pointed out. Thanks for the advice... Regards Alex "Bob Phillips" wrote: "Alex" wrote in message ... Bob Thanks for your note. On your points... (1) I wasn't aware that there were errors in the serial date function. Thanks for drawing that to my attention. Sorry, it appears I was not clear here. I am not saying there are errors in the serial date function, just that it is much easier to make a mistake in giving a serial date rather than the date date. For example, you said the serial date for Dec 1st 1979 is 29220. I believe you will find that it is actually 29190, you gave the serial date for 30th Dec 1979, making exactly the mistake that I believe is so easy to make :-). Not seeing it as a date makes this all to easy. Regards Bob |
#7
|
|||
|
|||
Both Phillips and Alex are u so thanks that u have solve my problem and u
have quiet good discussion over that but dear Bob phillips i can't follow u , i mean how 2 do the formula where should i put the formula etc would u like to help me further. i will appreciate it . and one thing more that i don't understand =IF(IF(B11=4,DATE(B11,1,1),B11)--"1979-12-01","UNDERAGE","ADULT") thanks minus after -- make me understand completely if u can cause i am newer to Excel thanks "Bob Phillips" wrote: Alex, I would never recommend using the serial date, it is error prone, and it just makes it far too difficult to understand. For instance, I make 1st Dec 1979 is 29190 :-). There are better ways, such as DATE(1979,12,01), or, my preference, --"1979-12-01". The other thing I would do is make some assumption about years, such as first day of year, so I would end up with =IF(IF(B11=4,DATE(B11,1,1),B11)--"1979-12-01","UNDERAGE","ADULT") -- HTH RP (remove nothere from the email address if mailing direct) "Alex" wrote in message ... Hello Ok. Quite simple I think. The 'serial' date for 01-Dec-1979 in Excel is 29220. Excel uses serial numbers to store dates. All you need to know is that 01-Dec-1979 = 29220. As I undertand it you have a list of dates in column C and want to assess whther they are 'UNDERAGE' or 'ADULT' using 01-Dec-1979 as the cut-off. Column C Column D 1-Jan-1980 =formula 23-Jul-1985 03-Apr-2002 1980 03-Nov-1996 1958 In column D1 (where I have put = formula), type in the formula as shown below and then drag down to calculate for all cells. =IF(LEN(B46)4,IF(B4629220,"UNDERAGE","ADULT"),IF (B46=1980,"UNDERAGE","ADU LT")) [NB - the formula is all one line - it appears as two lines here due to space constaints) This will take care of dates whether they are of the format dd-mm-yyyy or just simply yyyy. I hope this helps. If there are any problems then please write back and I shall endeavour to assist you further. Regards Alex "reneabesmer" wrote: so dear friends i am working in an NGOs here in Afghanistan and here i have my data about populations and those whos age is less then 31-dec-1979 are called underage and the rest of called Adult so how can find the correct result , my date in age Column in Excel sheet is below Column c 1-jan-1980 23-jul-1985 3-apr-2002 1980 3-nov-1996 1958 so i have such kind of data . so plz i really need ur help. that must be appreciated. thanks alot Looking forward for Solution |
#8
|
|||
|
|||
Hello again.
Bob Phillips knows more than I do so hopefully he can verify my post. I believe the double minus "--" is used for 'numerical coercion'. In your formula the "1979-12-01" is entered as Text. It is not in numerical format. To 'force' the conversion of the Text format to numerical format the -- is used. This then allows the formula to compare dates as both are in numerical format. As a different example, consider the following formula. Suppose cell A1 = 100. =ISNUMBER(A1) This will return "TRUE" as cell A1 is a number i.e. a 100. However, if you type... =ISNUMBER--(A1) This will return 1. This converts the TRUE to its Boolean equivalent i.e. 1. [To expand, in Boolean notation TRUE=1 and FALSE=0] Hopefully this helps you understand. In my limited Excel experience, the use of "--" is quite rare (or for professionals only) so I wouldn't worry about it too much. In general Excel formulas are much easier to understand. In answer of where to put the formula, it should go adjacent to the cell you wnat to test for Underage/Adult. So if your data starts in cell C1 then type formula in cell D1. Then just copy down fo other cells. Regards Alex "reneabesmer" wrote: Both Phillips and Alex are u so thanks that u have solve my problem and u have quiet good discussion over that but dear Bob phillips i can't follow u , i mean how 2 do the formula where should i put the formula etc would u like to help me further. i will appreciate it . and one thing more that i don't understand =IF(IF(B11=4,DATE(B11,1,1),B11)--"1979-12-01","UNDERAGE","ADULT") thanks minus after -- make me understand completely if u can cause i am newer to Excel thanks "Bob Phillips" wrote: Alex, I would never recommend using the serial date, it is error prone, and it just makes it far too difficult to understand. For instance, I make 1st Dec 1979 is 29190 :-). There are better ways, such as DATE(1979,12,01), or, my preference, --"1979-12-01". The other thing I would do is make some assumption about years, such as first day of year, so I would end up with =IF(IF(B11=4,DATE(B11,1,1),B11)--"1979-12-01","UNDERAGE","ADULT") -- HTH RP (remove nothere from the email address if mailing direct) "Alex" wrote in message ... Hello Ok. Quite simple I think. The 'serial' date for 01-Dec-1979 in Excel is 29220. Excel uses serial numbers to store dates. All you need to know is that 01-Dec-1979 = 29220. As I undertand it you have a list of dates in column C and want to assess whther they are 'UNDERAGE' or 'ADULT' using 01-Dec-1979 as the cut-off. Column C Column D 1-Jan-1980 =formula 23-Jul-1985 03-Apr-2002 1980 03-Nov-1996 1958 In column D1 (where I have put = formula), type in the formula as shown below and then drag down to calculate for all cells. =IF(LEN(B46)4,IF(B4629220,"UNDERAGE","ADULT"),IF (B46=1980,"UNDERAGE","ADU LT")) [NB - the formula is all one line - it appears as two lines here due to space constaints) This will take care of dates whether they are of the format dd-mm-yyyy or just simply yyyy. I hope this helps. If there are any problems then please write back and I shall endeavour to assist you further. Regards Alex "reneabesmer" wrote: so dear friends i am working in an NGOs here in Afghanistan and here i have my data about populations and those whos age is less then 31-dec-1979 are called underage and the rest of called Adult so how can find the correct result , my date in age Column in Excel sheet is below Column c 1-jan-1980 23-jul-1985 3-apr-2002 1980 3-nov-1996 1958 so i have such kind of data . so plz i really need ur help. that must be appreciated. thanks alot Looking forward for Solution |
#9
|
|||
|
|||
Hi Alex,
"Alex" wrote in message ... I believe the double minus "--" is used for 'numerical coercion'. In your formula the "1979-12-01" is entered as Text. It is not in numerical format. To 'force' the conversion of the Text format to numerical format the -- is used. This then allows the formula to compare dates as both are in numerical format. The -- is generally referred to as a double unary, but in essence you have got it right. As I said in my original post, we could use Date(1979,12,01), but I just think putting it in date format is more user-friendly, which needs the double unary, and I use th yyyy-mm-dd format as it is unambiguous (I don't know where you reside, but I am UK based and we have all sorts of problems with US centric dates) and it is ISO standard., As a different example, consider the following formula. Suppose cell A1 = 100. =ISNUMBER(A1) This will return "TRUE" as cell A1 is a number i.e. a 100. However, if you type... =ISNUMBER--(A1) This will return 1. This converts the TRUE to its Boolean equivalent i.e. 1. [To expand, in Boolean notation TRUE=1 and FALSE=0] Not quite. Your logic is correct, you implementation is a bit wrong. The formula should be =--ISNUMBER(A1) I think that is actually what you menat from the explanationn, probably just a typo. In answer of where to put the formula, it should go adjacent to the cell you wnat to test for Underage/Adult. So if your data starts in cell C1 then type formula in cell D1. Then just copy down fo other cells. Yeah, I was assuming that the adjacent cell with the date in was B11, so this was the conversion formula for B11, which would then be copied down. Regards Bob |
#10
|
|||
|
|||
thanks Bob i do understand what is mean by -- but still i have the problem 2
understand rest of the formula. and one thing 2 remind u that we OSI Date. let me explain my problem again with the formula Column A Column B 1-jan-1980 =IF(IF(A2=4,DATE(A2,1,1),A2)--"1979-12-01","UNDERAGE","ADULT") 1979 15-apr-1958 it gives me the correct result so thanks alot for that but dear friend would u like 2 explain =if(if(A2=4,date(A2,1,1),A2) plz i need some detail of it. i really Appreciate ur help "reneabesmer" wrote: so dear friends i am working in an NGOs here in Afghanistan and here i have my data about populations and those whos age is less then 31-dec-1979 are called underage and the rest of called Adult so how can find the correct result , my date in age Column in Excel sheet is below Column c 1-jan-1980 23-jul-1985 3-apr-2002 1980 3-nov-1996 1958 so i have such kind of data . so plz i really need ur help. that must be appreciated. thanks alot Looking forward for Solution |
#11
|
|||
|
|||
Okay I will try
I am using an IF to get a value that is used within an outer IF with this code IF(A2=4,DATE(A2,1,1),A2). What it says is that if A2 is only 4 digits, i,e just a year assume a date that is 1st Jan of that year, else use the whole date. The value returned is then just compared against our target date for under-ageness. -- HTH RP (remove nothere from the email address if mailing direct) "reneabesmer" wrote in message ... thanks Bob i do understand what is mean by -- but still i have the problem 2 understand rest of the formula. and one thing 2 remind u that we OSI Date. let me explain my problem again with the formula Column A Column B 1-jan-1980 =IF(IF(A2=4,DATE(A2,1,1),A2)--"1979-12-01","UNDERAGE","ADULT") 1979 15-apr-1958 it gives me the correct result so thanks alot for that but dear friend would u like 2 explain =if(if(A2=4,date(A2,1,1),A2) plz i need some detail of it. i really Appreciate ur help "reneabesmer" wrote: so dear friends i am working in an NGOs here in Afghanistan and here i have my data about populations and those whos age is less then 31-dec-1979 are called underage and the rest of called Adult so how can find the correct result , my date in age Column in Excel sheet is below Column c 1-jan-1980 23-jul-1985 3-apr-2002 1980 3-nov-1996 1958 so i have such kind of data . so plz i really need ur help. that must be appreciated. thanks alot Looking forward for Solution |
#12
|
|||
|
|||
Bob Phillips i have again the problem ur formula don't work in Excel when i
import Access Table to Ms Excel and then when i apply ur formula on Ms Access Table in Ms Excel i don't know whyyy could u help me plzzzzzzzzzzz thankx "Bob Phillips" wrote: Okay I will try I am using an IF to get a value that is used within an outer IF with this code IF(A2=4,DATE(A2,1,1),A2). What it says is that if A2 is only 4 digits, i,e just a year assume a date that is 1st Jan of that year, else use the whole date. The value returned is then just compared against our target date for under-ageness. -- HTH RP (remove nothere from the email address if mailing direct) "reneabesmer" wrote in message ... thanks Bob i do understand what is mean by -- but still i have the problem 2 understand rest of the formula. and one thing 2 remind u that we OSI Date. let me explain my problem again with the formula Column A Column B 1-jan-1980 =IF(IF(A2=4,DATE(A2,1,1),A2)--"1979-12-01","UNDERAGE","ADULT") 1979 15-apr-1958 it gives me the correct result so thanks alot for that but dear friend would u like 2 explain =if(if(A2=4,date(A2,1,1),A2) plz i need some detail of it. i really Appreciate ur help "reneabesmer" wrote: so dear friends i am working in an NGOs here in Afghanistan and here i have my data about populations and those whos age is less then 31-dec-1979 are called underage and the rest of called Adult so how can find the correct result , my date in age Column in Excel sheet is below Column c 1-jan-1980 23-jul-1985 3-apr-2002 1980 3-nov-1996 1958 so i have such kind of data . so plz i really need ur help. that must be appreciated. thanks alot Looking forward for Solution |
#13
|
|||
|
|||
Is it because the dates are not real dates, but text?
Here is a nother variation to try =IF(IF(A2=4,DATE(A2,1,1),DATEVALUE(A2))--"1979-12-01","UNDERAGE","ADULT") -- HTH RP (remove nothere from the email address if mailing direct) "reneabesmer" wrote in message ... Bob Phillips i have again the problem ur formula don't work in Excel when i import Access Table to Ms Excel and then when i apply ur formula on Ms Access Table in Ms Excel i don't know whyyy could u help me plzzzzzzzzzzz thankx "Bob Phillips" wrote: Okay I will try I am using an IF to get a value that is used within an outer IF with this code IF(A2=4,DATE(A2,1,1),A2). What it says is that if A2 is only 4 digits, i,e just a year assume a date that is 1st Jan of that year, else use the whole date. The value returned is then just compared against our target date for under-ageness. -- HTH RP (remove nothere from the email address if mailing direct) "reneabesmer" wrote in message ... thanks Bob i do understand what is mean by -- but still i have the problem 2 understand rest of the formula. and one thing 2 remind u that we OSI Date. let me explain my problem again with the formula Column A Column B 1-jan-1980 =IF(IF(A2=4,DATE(A2,1,1),A2)--"1979-12-01","UNDERAGE","ADULT") 1979 15-apr-1958 it gives me the correct result so thanks alot for that but dear friend would u like 2 explain =if(if(A2=4,date(A2,1,1),A2) plz i need some detail of it. i really Appreciate ur help "reneabesmer" wrote: so dear friends i am working in an NGOs here in Afghanistan and here i have my data about populations and those whos age is less then 31-dec-1979 are called underage and the rest of called Adult so how can find the correct result , my date in age Column in Excel sheet is below Column c 1-jan-1980 23-jul-1985 3-apr-2002 1980 3-nov-1996 1958 so i have such kind of data . so plz i really need ur help. that must be appreciated. thanks alot Looking forward for Solution |
#14
|
|||
|
|||
Hiii Sir
it's still don't work my dear sir Bob Phillips so what i can do , i am so confuse about it and i am been working manually for Adult and underage people that really sucks hope u will help me soon. thanks "Bob Phillips" wrote: Is it because the dates are not real dates, but text? Here is a nother variation to try =IF(IF(A2=4,DATE(A2,1,1),DATEVALUE(A2))--"1979-12-01","UNDERAGE","ADULT") -- HTH RP (remove nothere from the email address if mailing direct) "reneabesmer" wrote in message ... Bob Phillips i have again the problem ur formula don't work in Excel when i import Access Table to Ms Excel and then when i apply ur formula on Ms Access Table in Ms Excel i don't know whyyy could u help me plzzzzzzzzzzz thankx "Bob Phillips" wrote: Okay I will try I am using an IF to get a value that is used within an outer IF with this code IF(A2=4,DATE(A2,1,1),A2). What it says is that if A2 is only 4 digits, i,e just a year assume a date that is 1st Jan of that year, else use the whole date. The value returned is then just compared against our target date for under-ageness. -- HTH RP (remove nothere from the email address if mailing direct) "reneabesmer" wrote in message ... thanks Bob i do understand what is mean by -- but still i have the problem 2 understand rest of the formula. and one thing 2 remind u that we OSI Date. let me explain my problem again with the formula Column A Column B 1-jan-1980 =IF(IF(A2=4,DATE(A2,1,1),A2)--"1979-12-01","UNDERAGE","ADULT") 1979 15-apr-1958 it gives me the correct result so thanks alot for that but dear friend would u like 2 explain =if(if(A2=4,date(A2,1,1),A2) plz i need some detail of it. i really Appreciate ur help "reneabesmer" wrote: so dear friends i am working in an NGOs here in Afghanistan and here i have my data about populations and those whos age is less then 31-dec-1979 are called underage and the rest of called Adult so how can find the correct result , my date in age Column in Excel sheet is below Column c 1-jan-1980 23-jul-1985 3-apr-2002 1980 3-nov-1996 1958 so i have such kind of data . so plz i really need ur help. that must be appreciated. thanks alot Looking forward for Solution |
#15
|
|||
|
|||
Hi Alex and Bob this is Gio from Philippines...... it's my first time here in
this site... i am wondering how can i solve this problem in excel and please help me.. I used excel 2000 in creating an inventory program in the hospital. I used this excel inventorry program in our suppply room, i used one worksheet per item. and i have almost 300 items in the supply room or almost 300 worksheets. I saved it as a template for all i know it is safer to save it as template rather than saving it as ordinary excel files. The program was working well, but not when i started linking(hyperlink) it from a certain file that i always used. Then i have save it several times as a template but i notice that the program malfuncitons, it doesnt compute the formulas i created and some formulas are gone. Why is this happening. when i add some items in the inventory it wouldnt add to the current balance, why is this happening? Will you please help me, you wer the only people who can only help me with this kind of problem......please.... |
#16
|
|||
|
|||
Hi Alex and Bob this is Gio from Philippines...... it's my first time here in
this site... i am wondering how can i solve this problem in excel and please help me.. I used excel 2000 in creating an inventory program in the hospital. I used this excel inventorry program in our suppply room, i used one worksheet per item. and i have almost 300 items in the supply room or almost 300 worksheets. I saved it as a template for all i know it is safer to save it as template rather than saving it as ordinary excel files. The program was working well, but not when i started linking(hyperlink) it from a certain file that i always used. Then i have save it several times as a template but i notice that the program malfuncitons, it doesnt compute the formulas i created and some formulas are gone. Why is this happening. when i add some items in the inventory it wouldnt add to the current balance, why is this happening? Will you please help me, you wer the only people who can only help me with this kind of problem......please.... |
#17
|
|||
|
|||
Send me a workbook to look at.
-- HTH RP (remove nothere from the email address if mailing direct) "reneabesmer" wrote in message ... Hiii Sir it's still don't work my dear sir Bob Phillips so what i can do , i am so confuse about it and i am been working manually for Adult and underage people that really sucks hope u will help me soon. thanks "Bob Phillips" wrote: Is it because the dates are not real dates, but text? Here is a nother variation to try =IF(IF(A2=4,DATE(A2,1,1),DATEVALUE(A2))--"1979-12-01","UNDERAGE","ADULT") -- HTH RP (remove nothere from the email address if mailing direct) "reneabesmer" wrote in message ... Bob Phillips i have again the problem ur formula don't work in Excel when i import Access Table to Ms Excel and then when i apply ur formula on Ms Access Table in Ms Excel i don't know whyyy could u help me plzzzzzzzzzzz thankx "Bob Phillips" wrote: Okay I will try I am using an IF to get a value that is used within an outer IF with this code IF(A2=4,DATE(A2,1,1),A2). What it says is that if A2 is only 4 digits, i,e just a year assume a date that is 1st Jan of that year, else use the whole date. The value returned is then just compared against our target date for under-ageness. -- HTH RP (remove nothere from the email address if mailing direct) "reneabesmer" wrote in message ... thanks Bob i do understand what is mean by -- but still i have the problem 2 understand rest of the formula. and one thing 2 remind u that we OSI Date. let me explain my problem again with the formula Column A Column B 1-jan-1980 =IF(IF(A2=4,DATE(A2,1,1),A2)--"1979-12-01","UNDERAGE","ADULT") 1979 15-apr-1958 it gives me the correct result so thanks alot for that but dear friend would u like 2 explain =if(if(A2=4,date(A2,1,1),A2) plz i need some detail of it. i really Appreciate ur help "reneabesmer" wrote: so dear friends i am working in an NGOs here in Afghanistan and here i have my data about populations and those whos age is less then 31-dec-1979 are called underage and the rest of called Adult so how can find the correct result , my date in age Column in Excel sheet is below Column c 1-jan-1980 23-jul-1985 3-apr-2002 1980 3-nov-1996 1958 so i have such kind of data . so plz i really need ur help. that must be appreciated. thanks alot Looking forward for Solution |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search for latest date | Excel Worksheet Functions | |||
How can I find and sort specific data within a column? | Excel Discussion (Misc queries) | |||
Need to find oldest date in ever changing list. | Excel Worksheet Functions | |||
cannot find database | Links and Linking in Excel | |||
Need to find Min value based on date range entered | Excel Worksheet Functions |