date formulas
 

Hi, I have two problems that need help.

Cell A1 has a date of birth. Cell A2 calculates the age. If there isn't a
dob of birth, cell A2 returns 110.

Another one is having cell C1 with a date or NA. C2 is based on cell C1 and
adds 3 months to the date in C1 but if C1 has NA, the return is #value!.

Kathleen

date formulas
 

Describing the problem is only the first step, Kathleen. You also need to
identify the solution you are looking for.

As a guess, for #1 try,
=if(a1="","",datedif(a1,today(),"y"))

For #2,
=if(c1="NA","",date(year(c1),month(c1)+3,day(c1))

Regards,
Fred

"Kathleen" wrote in message
...
Hi, I have two problems that need help.

Cell A1 has a date of birth. Cell A2 calculates the age. If there isn't
a
dob of birth, cell A2 returns 110.

Another one is having cell C1 with a date or NA. C2 is based on cell C1
and
adds 3 months to the date in C1 but if C1 has NA, the return is #value!.

Kathleen

date formulas
 

Hi Fred, Sorry about that...and thank you for trying. I'm using Exel 2003.

What I am trying to do is get the age or the return of na. AR5 has the date
of birth. AR1 has the formula to calculate the age based on the date of
birth.

AR5 = 07/29/1965
AR1 = 44

but if AR5 is blank, AR1 is returning 110. Is it possible to have a return
of na instead? Here's the formula I'm using =DATEDIF(AR5,NOW(),"y")


In problem two, I am using this formula in HA6 to add three months to a the
date in HA5. Problem is that HA5 could have a date or be marked NA. If HA5
is na, the return is #value!. Is it possible to have it return na?
=MIN(DATE(YEAR(HA5),MONTH(HA5)+3+{0,1},DAY(HA5)*{0 ,0}))



"Fred Smith" wrote:

Describing the problem is only the first step, Kathleen. You also need to
identify the solution you are looking for.

As a guess, for #1 try,
=if(a1="","",datedif(a1,today(),"y"))

For #2,
=if(c1="NA","",date(year(c1),month(c1)+3,day(c1))

Regards,
Fred

"Kathleen" wrote in message
...
Hi, I have two problems that need help.

Cell A1 has a date of birth. Cell A2 calculates the age. If there isn't
a
dob of birth, cell A2 returns 110.

Another one is having cell C1 with a date or NA. C2 is based on cell C1
and
adds 3 months to the date in C1 but if C1 has NA, the return is #value!.

Kathleen

.

date formulas
 

So my guess for #1 was correct. You want:
=if(ar5="","na",datedif(ar5,today(),"y"))

And my guess for #2 was correct. You want:
=if(ha5="na","na",date(year(ha5),month(ha5)+3,day( ha5))

Regards,
Fred

"Kathleen" wrote in message
...
Hi Fred, Sorry about that...and thank you for trying. I'm using Exel
2003.

What I am trying to do is get the age or the return of na. AR5 has the
date
of birth. AR1 has the formula to calculate the age based on the date of
birth.

AR5 = 07/29/1965
AR1 = 44

but if AR5 is blank, AR1 is returning 110. Is it possible to have a
return
of na instead? Here's the formula I'm using =DATEDIF(AR5,NOW(),"y")


In problem two, I am using this formula in HA6 to add three months to a
the
date in HA5. Problem is that HA5 could have a date or be marked NA. If
HA5
is na, the return is #value!. Is it possible to have it return na?
=MIN(DATE(YEAR(HA5),MONTH(HA5)+3+{0,1},DAY(HA5)*{0 ,0}))



"Fred Smith" wrote:

Describing the problem is only the first step, Kathleen. You also need to
identify the solution you are looking for.

As a guess, for #1 try,
=if(a1="","",datedif(a1,today(),"y"))

For #2,
=if(c1="NA","",date(year(c1),month(c1)+3,day(c1))

Regards,
Fred

"Kathleen" wrote in message
...
Hi, I have two problems that need help.

Cell A1 has a date of birth. Cell A2 calculates the age. If there
isn't
a
dob of birth, cell A2 returns 110.

Another one is having cell C1 with a date or NA. C2 is based on cell
C1
and
adds 3 months to the date in C1 but if C1 has NA, the return is
#value!.

Kathleen

.

date formulas
 

Fred your the best!! Thank you! This helps to make the file look a lot less
confusing to the users.

"Fred Smith" wrote:

So my guess for #1 was correct. You want:
=if(ar5="","na",datedif(ar5,today(),"y"))

And my guess for #2 was correct. You want:
=if(ha5="na","na",date(year(ha5),month(ha5)+3,day( ha5))

Regards,
Fred

"Kathleen" wrote in message
...
Hi Fred, Sorry about that...and thank you for trying. I'm using Exel
2003.

What I am trying to do is get the age or the return of na. AR5 has the
date
of birth. AR1 has the formula to calculate the age based on the date of
birth.

AR5 = 07/29/1965
AR1 = 44

but if AR5 is blank, AR1 is returning 110. Is it possible to have a
return
of na instead? Here's the formula I'm using =DATEDIF(AR5,NOW(),"y")


In problem two, I am using this formula in HA6 to add three months to a
the
date in HA5. Problem is that HA5 could have a date or be marked NA. If
HA5
is na, the return is #value!. Is it possible to have it return na?
=MIN(DATE(YEAR(HA5),MONTH(HA5)+3+{0,1},DAY(HA5)*{0 ,0}))



"Fred Smith" wrote:

Describing the problem is only the first step, Kathleen. You also need to
identify the solution you are looking for.

As a guess, for #1 try,
=if(a1="","",datedif(a1,today(),"y"))

For #2,
=if(c1="NA","",date(year(c1),month(c1)+3,day(c1))

Regards,
Fred

"Kathleen" wrote in message
...
Hi, I have two problems that need help.

Cell A1 has a date of birth. Cell A2 calculates the age. If there
isn't
a
dob of birth, cell A2 returns 110.

Another one is having cell C1 with a date or NA. C2 is based on cell
C1
and
adds 3 months to the date in C1 but if C1 has NA, the return is
#value!.

Kathleen

.


.

date formulas
 

Glad to help. Thanks for the feedback.

Regards,
Fred

"Kathleen" wrote in message
...
Fred your the best!! Thank you! This helps to make the file look a lot
less
confusing to the users.

"Fred Smith" wrote:

So my guess for #1 was correct. You want:
=if(ar5="","na",datedif(ar5,today(),"y"))

And my guess for #2 was correct. You want:
=if(ha5="na","na",date(year(ha5),month(ha5)+3,day( ha5))

Regards,
Fred

"Kathleen" wrote in message
...
Hi Fred, Sorry about that...and thank you for trying. I'm using Exel
2003.

What I am trying to do is get the age or the return of na. AR5 has the
date
of birth. AR1 has the formula to calculate the age based on the date
of
birth.

AR5 = 07/29/1965
AR1 = 44

but if AR5 is blank, AR1 is returning 110. Is it possible to have a
return
of na instead? Here's the formula I'm using =DATEDIF(AR5,NOW(),"y")


In problem two, I am using this formula in HA6 to add three months to a
the
date in HA5. Problem is that HA5 could have a date or be marked NA.
If
HA5
is na, the return is #value!. Is it possible to have it return na?
=MIN(DATE(YEAR(HA5),MONTH(HA5)+3+{0,1},DAY(HA5)*{0 ,0}))



"Fred Smith" wrote:

Describing the problem is only the first step, Kathleen. You also need
to
identify the solution you are looking for.

As a guess, for #1 try,
=if(a1="","",datedif(a1,today(),"y"))

For #2,
=if(c1="NA","",date(year(c1),month(c1)+3,day(c1))

Regards,
Fred

"Kathleen" wrote in message
...
Hi, I have two problems that need help.

Cell A1 has a date of birth. Cell A2 calculates the age. If there
isn't
a
dob of birth, cell A2 returns 110.

Another one is having cell C1 with a date or NA. C2 is based on
cell
C1
and
adds 3 months to the date in C1 but if C1 has NA, the return is
#value!.

Kathleen

.


.

date formulas
 

Hi,

The formula is working great but now I've hit another snag based on that
result. If AR3 is blank, then I can return NA, or in the case below 0.
Problem is that GC4 looks at AR3 to see if the number is greater/equal to 65.
But when AR3 is NA or 0, GC4 is returning YES, instead of no. I can
understand why GC4 wouldn't recognize NA but why won't it work if GC4 is 0?


=IF(AR3="","0",DATEDIF(AR3,TODAY(),"y"))

=IF(GC4:GC4=65,"YES","NO")

"Fred Smith" wrote:

Glad to help. Thanks for the feedback.

Regards,
Fred

"Kathleen" wrote in message
...
Fred your the best!! Thank you! This helps to make the file look a lot
less
confusing to the users.

"Fred Smith" wrote:

So my guess for #1 was correct. You want:
=if(ar5="","na",datedif(ar5,today(),"y"))

And my guess for #2 was correct. You want:
=if(ha5="na","na",date(year(ha5),month(ha5)+3,day( ha5))

Regards,
Fred

"Kathleen" wrote in message
...
Hi Fred, Sorry about that...and thank you for trying. I'm using Exel
2003.

What I am trying to do is get the age or the return of na. AR5 has the
date
of birth. AR1 has the formula to calculate the age based on the date
of
birth.

AR5 = 07/29/1965
AR1 = 44

but if AR5 is blank, AR1 is returning 110. Is it possible to have a
return
of na instead? Here's the formula I'm using =DATEDIF(AR5,NOW(),"y")


In problem two, I am using this formula in HA6 to add three months to a
the
date in HA5. Problem is that HA5 could have a date or be marked NA.
If
HA5
is na, the return is #value!. Is it possible to have it return na?
=MIN(DATE(YEAR(HA5),MONTH(HA5)+3+{0,1},DAY(HA5)*{0 ,0}))



"Fred Smith" wrote:

Describing the problem is only the first step, Kathleen. You also need
to
identify the solution you are looking for.

As a guess, for #1 try,
=if(a1="","",datedif(a1,today(),"y"))

For #2,
=if(c1="NA","",date(year(c1),month(c1)+3,day(c1))

Regards,
Fred

"Kathleen" wrote in message
...
Hi, I have two problems that need help.

Cell A1 has a date of birth. Cell A2 calculates the age. If there
isn't
a
dob of birth, cell A2 returns 110.

Another one is having cell C1 with a date or NA. C2 is based on
cell
C1
and
adds 3 months to the date in C1 but if C1 has NA, the return is
#value!.

Kathleen

.


.


.

date formulas
 

It would work if GC4 were 0, but not if it is "0". The quote marks
designate a text string, not a number.
So try changing =IF(AR3="","0",DATEDIF(AR3,TODAY(),"y")) to
=IF(AR3="",,DATEDIF(AR3,TODAY(),"y"))
Also, your =IF(GC4:GC4=65,"YES","NO") should probably be
=IF(GC4=65,"YES","NO") as you don't need the array GC4:GC4
--
David Biddulph


"Kathleen" wrote in message
...
Hi,

The formula is working great but now I've hit another snag based on that
result. If AR3 is blank, then I can return NA, or in the case below 0.
Problem is that GC4 looks at AR3 to see if the number is greater/equal to
65.
But when AR3 is NA or 0, GC4 is returning YES, instead of no. I can
understand why GC4 wouldn't recognize NA but why won't it work if GC4 is
0?


=IF(AR3="","0",DATEDIF(AR3,TODAY(),"y"))

=IF(GC4:GC4=65,"YES","NO")

"Fred Smith" wrote:

Glad to help. Thanks for the feedback.

Regards,
Fred

"Kathleen" wrote in message
...
Fred your the best!! Thank you! This helps to make the file look a lot
less
confusing to the users.

"Fred Smith" wrote:

So my guess for #1 was correct. You want:
=if(ar5="","na",datedif(ar5,today(),"y"))

And my guess for #2 was correct. You want:
=if(ha5="na","na",date(year(ha5),month(ha5)+3,day( ha5))

Regards,
Fred

"Kathleen" wrote in message
...
Hi Fred, Sorry about that...and thank you for trying. I'm using
Exel
2003.

What I am trying to do is get the age or the return of na. AR5 has
the
date
of birth. AR1 has the formula to calculate the age based on the
date
of
birth.

AR5 = 07/29/1965
AR1 = 44

but if AR5 is blank, AR1 is returning 110. Is it possible to have a
return
of na instead? Here's the formula I'm using
=DATEDIF(AR5,NOW(),"y")


In problem two, I am using this formula in HA6 to add three months
to a
the
date in HA5. Problem is that HA5 could have a date or be marked NA.
If
HA5
is na, the return is #value!. Is it possible to have it return na?
=MIN(DATE(YEAR(HA5),MONTH(HA5)+3+{0,1},DAY(HA5)*{0 ,0}))



"Fred Smith" wrote:

Describing the problem is only the first step, Kathleen. You also
need
to
identify the solution you are looking for.

As a guess, for #1 try,
=if(a1="","",datedif(a1,today(),"y"))

For #2,
=if(c1="NA","",date(year(c1),month(c1)+3,day(c1))

Regards,
Fred

"Kathleen" wrote in message
...
Hi, I have two problems that need help.

Cell A1 has a date of birth. Cell A2 calculates the age. If
there
isn't
a
dob of birth, cell A2 returns 110.

Another one is having cell C1 with a date or NA. C2 is based on
cell
C1
and
adds 3 months to the date in C1 but if C1 has NA, the return is
#value!.

Kathleen

.


.


.

date formulas
 

I would prefer:
=IF(AR3="",0,DATEDIF(AR3,TODAY(),"y"))
as it's clearer what is wanted when AR3 is blank,
but both will work.

Regards,
Fred

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
It would work if GC4 were 0, but not if it is "0". The quote marks
designate a text string, not a number.
So try changing =IF(AR3="","0",DATEDIF(AR3,TODAY(),"y")) to
=IF(AR3="",,DATEDIF(AR3,TODAY(),"y"))
Also, your =IF(GC4:GC4=65,"YES","NO") should probably be
=IF(GC4=65,"YES","NO") as you don't need the array GC4:GC4
--
David Biddulph


"Kathleen" wrote in message
...
Hi,

The formula is working great but now I've hit another snag based on that
result. If AR3 is blank, then I can return NA, or in the case below 0.
Problem is that GC4 looks at AR3 to see if the number is greater/equal to
65.
But when AR3 is NA or 0, GC4 is returning YES, instead of no. I can
understand why GC4 wouldn't recognize NA but why won't it work if GC4 is
0?


=IF(AR3="","0",DATEDIF(AR3,TODAY(),"y"))

=IF(GC4:GC4=65,"YES","NO")

"Fred Smith" wrote:

Glad to help. Thanks for the feedback.

Regards,
Fred

"Kathleen" wrote in message
...
Fred your the best!! Thank you! This helps to make the file look a
lot
less
confusing to the users.

"Fred Smith" wrote:

So my guess for #1 was correct. You want:
=if(ar5="","na",datedif(ar5,today(),"y"))

And my guess for #2 was correct. You want:
=if(ha5="na","na",date(year(ha5),month(ha5)+3,day( ha5))

Regards,
Fred

"Kathleen" wrote in message
...
Hi Fred, Sorry about that...and thank you for trying. I'm using
Exel
2003.

What I am trying to do is get the age or the return of na. AR5 has
the
date
of birth. AR1 has the formula to calculate the age based on the
date
of
birth.

AR5 = 07/29/1965
AR1 = 44

but if AR5 is blank, AR1 is returning 110. Is it possible to have
a
return
of na instead? Here's the formula I'm using
=DATEDIF(AR5,NOW(),"y")


In problem two, I am using this formula in HA6 to add three months
to a
the
date in HA5. Problem is that HA5 could have a date or be marked
NA.
If
HA5
is na, the return is #value!. Is it possible to have it return na?
=MIN(DATE(YEAR(HA5),MONTH(HA5)+3+{0,1},DAY(HA5)*{0 ,0}))



"Fred Smith" wrote:

Describing the problem is only the first step, Kathleen. You also
need
to
identify the solution you are looking for.

As a guess, for #1 try,
=if(a1="","",datedif(a1,today(),"y"))

For #2,
=if(c1="NA","",date(year(c1),month(c1)+3,day(c1))

Regards,
Fred

"Kathleen" wrote in message
...
Hi, I have two problems that need help.

Cell A1 has a date of birth. Cell A2 calculates the age. If
there
isn't
a
dob of birth, cell A2 returns 110.

Another one is having cell C1 with a date or NA. C2 is based on
cell
C1
and
adds 3 months to the date in C1 but if C1 has NA, the return is
#value!.

Kathleen

.


.


.

date formulas
 

Thank you!

"Fred Smith" wrote:

I would prefer:
=IF(AR3="",0,DATEDIF(AR3,TODAY(),"y"))
as it's clearer what is wanted when AR3 is blank,
but both will work.

Regards,
Fred

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
It would work if GC4 were 0, but not if it is "0". The quote marks
designate a text string, not a number.
So try changing =IF(AR3="","0",DATEDIF(AR3,TODAY(),"y")) to
=IF(AR3="",,DATEDIF(AR3,TODAY(),"y"))
Also, your =IF(GC4:GC4=65,"YES","NO") should probably be
=IF(GC4=65,"YES","NO") as you don't need the array GC4:GC4
--
David Biddulph


"Kathleen" wrote in message
...
Hi,

The formula is working great but now I've hit another snag based on that
result. If AR3 is blank, then I can return NA, or in the case below 0.
Problem is that GC4 looks at AR3 to see if the number is greater/equal to
65.
But when AR3 is NA or 0, GC4 is returning YES, instead of no. I can
understand why GC4 wouldn't recognize NA but why won't it work if GC4 is
0?


=IF(AR3="","0",DATEDIF(AR3,TODAY(),"y"))

=IF(GC4:GC4=65,"YES","NO")

"Fred Smith" wrote:

Glad to help. Thanks for the feedback.

Regards,
Fred

"Kathleen" wrote in message
...
Fred your the best!! Thank you! This helps to make the file look a
lot
less
confusing to the users.

"Fred Smith" wrote:

So my guess for #1 was correct. You want:
=if(ar5="","na",datedif(ar5,today(),"y"))

And my guess for #2 was correct. You want:
=if(ha5="na","na",date(year(ha5),month(ha5)+3,day( ha5))

Regards,
Fred

"Kathleen" wrote in message
...
Hi Fred, Sorry about that...and thank you for trying. I'm using
Exel
2003.

What I am trying to do is get the age or the return of na. AR5 has
the
date
of birth. AR1 has the formula to calculate the age based on the
date
of
birth.

AR5 = 07/29/1965
AR1 = 44

but if AR5 is blank, AR1 is returning 110. Is it possible to have
a
return
of na instead? Here's the formula I'm using
=DATEDIF(AR5,NOW(),"y")


In problem two, I am using this formula in HA6 to add three months
to a
the
date in HA5. Problem is that HA5 could have a date or be marked
NA.
If
HA5
is na, the return is #value!. Is it possible to have it return na?
=MIN(DATE(YEAR(HA5),MONTH(HA5)+3+{0,1},DAY(HA5)*{0 ,0}))



"Fred Smith" wrote:

Describing the problem is only the first step, Kathleen. You also
need
to
identify the solution you are looking for.

As a guess, for #1 try,
=if(a1="","",datedif(a1,today(),"y"))

For #2,
=if(c1="NA","",date(year(c1),month(c1)+3,day(c1))

Regards,
Fred

"Kathleen" wrote in message
...
Hi, I have two problems that need help.

Cell A1 has a date of birth. Cell A2 calculates the age. If
there
isn't
a
dob of birth, cell A2 returns 110.

Another one is having cell C1 with a date or NA. C2 is based on
cell
C1
and
adds 3 months to the date in C1 but if C1 has NA, the return is
#value!.

Kathleen

.


.


.



.

date formulas
 

You're welcome. Thanks for the feedback.

Regards,
Fred

"Kathleen" wrote in message
...
Thank you!

"Fred Smith" wrote:

I would prefer:
=IF(AR3="",0,DATEDIF(AR3,TODAY(),"y"))
as it's clearer what is wanted when AR3 is blank,
but both will work.

Regards,
Fred

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
It would work if GC4 were 0, but not if it is "0". The quote marks
designate a text string, not a number.
So try changing =IF(AR3="","0",DATEDIF(AR3,TODAY(),"y")) to
=IF(AR3="",,DATEDIF(AR3,TODAY(),"y"))
Also, your =IF(GC4:GC4=65,"YES","NO") should probably be
=IF(GC4=65,"YES","NO") as you don't need the array GC4:GC4
--
David Biddulph


"Kathleen" wrote in message
...
Hi,

The formula is working great but now I've hit another snag based on
that
result. If AR3 is blank, then I can return NA, or in the case below
0.
Problem is that GC4 looks at AR3 to see if the number is greater/equal
to
65.
But when AR3 is NA or 0, GC4 is returning YES, instead of no. I can
understand why GC4 wouldn't recognize NA but why won't it work if GC4
is
0?


=IF(AR3="","0",DATEDIF(AR3,TODAY(),"y"))

=IF(GC4:GC4=65,"YES","NO")

"Fred Smith" wrote:

Glad to help. Thanks for the feedback.

Regards,
Fred

"Kathleen" wrote in message
...
Fred your the best!! Thank you! This helps to make the file look a
lot
less
confusing to the users.

"Fred Smith" wrote:

So my guess for #1 was correct. You want:
=if(ar5="","na",datedif(ar5,today(),"y"))

And my guess for #2 was correct. You want:
=if(ha5="na","na",date(year(ha5),month(ha5)+3,day( ha5))

Regards,
Fred

"Kathleen" wrote in message
...
Hi Fred, Sorry about that...and thank you for trying. I'm
using
Exel
2003.

What I am trying to do is get the age or the return of na. AR5
has
the
date
of birth. AR1 has the formula to calculate the age based on the
date
of
birth.

AR5 = 07/29/1965
AR1 = 44

but if AR5 is blank, AR1 is returning 110. Is it possible to
have
a
return
of na instead? Here's the formula I'm using
=DATEDIF(AR5,NOW(),"y")


In problem two, I am using this formula in HA6 to add three
months
to a
the
date in HA5. Problem is that HA5 could have a date or be marked
NA.
If
HA5
is na, the return is #value!. Is it possible to have it return
na?
=MIN(DATE(YEAR(HA5),MONTH(HA5)+3+{0,1},DAY(HA5)*{0 ,0}))



"Fred Smith" wrote:

Describing the problem is only the first step, Kathleen. You
also
need
to
identify the solution you are looking for.

As a guess, for #1 try,
=if(a1="","",datedif(a1,today(),"y"))

For #2,
=if(c1="NA","",date(year(c1),month(c1)+3,day(c1))

Regards,
Fred

"Kathleen" wrote in
message
...
Hi, I have two problems that need help.

Cell A1 has a date of birth. Cell A2 calculates the age. If
there
isn't
a
dob of birth, cell A2 returns 110.

Another one is having cell C1 with a date or NA. C2 is based
on
cell
C1
and
adds 3 months to the date in C1 but if C1 has NA, the return
is
#value!.

Kathleen

.


.


.



.