how can i find a specific date of a database

[reneabesmer]

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

 

[Alex]

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(B46>29220,"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

 

[Bob Phillips]

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)

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(B46>29220,"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

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

 

[Alex]

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

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)

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(B46>29220,"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

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

 

[Bob Phillips]

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

 

[Alex]

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

 

[reneabesmer]

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

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)

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(B46>29220,"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

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

 

[Alex]

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

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

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)

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(B46>29220,"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



:

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

 

[Bob Phillips]

Hi Alex,

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

 

[reneabesmer]

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

 

[Bob Phillips]

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]

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]

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]

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

 

[Giovanni D via OfficeKB.com]

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

 

[Giovanni D via OfficeKB.com]

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

 

[Bob Phillips]

Send me a workbook to look at.

--

HTH

RP
(remove nothere from the email address if mailing direct)