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
0
Utf
2/8/2010 3:36:01 AM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
771 Views

Similar Articles

[PageSpeed] 21

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" <Kathleen@discussions.microsoft.com> wrote in message 
news:D125DA8D-24B4-4BEA-BCD6-AB3D1C0312EC@microsoft.com...
> 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 

0
Fred
2/8/2010 4:15:13 AM
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" <Kathleen@discussions.microsoft.com> wrote in message 
> news:D125DA8D-24B4-4BEA-BCD6-AB3D1C0312EC@microsoft.com...
> > 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 
> 
> .
> 
0
Utf
2/22/2010 12:31:01 AM
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" <Kathleen@discussions.microsoft.com> wrote in message 
news:B06C536D-1802-48F6-9F57-F3C9636FF65A@microsoft.com...
> 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" <Kathleen@discussions.microsoft.com> wrote in message
>> news:D125DA8D-24B4-4BEA-BCD6-AB3D1C0312EC@microsoft.com...
>> > 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
>>
>> .
>> 

0
Fred
2/22/2010 12:48:15 AM
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" <Kathleen@discussions.microsoft.com> wrote in message 
> news:B06C536D-1802-48F6-9F57-F3C9636FF65A@microsoft.com...
> > 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" <Kathleen@discussions.microsoft.com> wrote in message
> >> news:D125DA8D-24B4-4BEA-BCD6-AB3D1C0312EC@microsoft.com...
> >> > 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
> >>
> >> .
> >> 
> 
> .
> 
0
Utf
2/23/2010 2:50:06 AM
Glad to help. Thanks for the feedback.

Regards,
Fred

"Kathleen" <Kathleen@discussions.microsoft.com> wrote in message 
news:B913757F-8894-4C05-86B9-F0962F1C989B@microsoft.com...
> 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" <Kathleen@discussions.microsoft.com> wrote in message
>> news:B06C536D-1802-48F6-9F57-F3C9636FF65A@microsoft.com...
>> > 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" <Kathleen@discussions.microsoft.com> wrote in message
>> >> news:D125DA8D-24B4-4BEA-BCD6-AB3D1C0312EC@microsoft.com...
>> >> > 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
>> >>
>> >> .
>> >>
>>
>> .
>> 

0
Fred
2/23/2010 4:08:10 PM
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" <Kathleen@discussions.microsoft.com> wrote in message 
> news:B913757F-8894-4C05-86B9-F0962F1C989B@microsoft.com...
> > 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" <Kathleen@discussions.microsoft.com> wrote in message
> >> news:B06C536D-1802-48F6-9F57-F3C9636FF65A@microsoft.com...
> >> > 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" <Kathleen@discussions.microsoft.com> wrote in message
> >> >> news:D125DA8D-24B4-4BEA-BCD6-AB3D1C0312EC@microsoft.com...
> >> >> > 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
> >> >>
> >> >> .
> >> >>
> >>
> >> .
> >> 
> 
> .
> 
0
Utf
2/24/2010 3:19:01 AM
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" <Kathleen@discussions.microsoft.com> wrote in message 
news:4B6A7B86-E4E8-4011-A68C-C09947A5A104@microsoft.com...
> 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" <Kathleen@discussions.microsoft.com> wrote in message
>> news:B913757F-8894-4C05-86B9-F0962F1C989B@microsoft.com...
>> > 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" <Kathleen@discussions.microsoft.com> wrote in message
>> >> news:B06C536D-1802-48F6-9F57-F3C9636FF65A@microsoft.com...
>> >> > 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" <Kathleen@discussions.microsoft.com> wrote in message
>> >> >> news:D125DA8D-24B4-4BEA-BCD6-AB3D1C0312EC@microsoft.com...
>> >> >> > 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
>> >> >>
>> >> >> .
>> >> >>
>> >>
>> >> .
>> >>
>>
>> .
>> 

0
David
2/24/2010 8:20:40 AM
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 
news:WMKdnThahNrFfBnWnZ2dnUVZ8sadnZ2d@bt.com...
> 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" <Kathleen@discussions.microsoft.com> wrote in message 
> news:4B6A7B86-E4E8-4011-A68C-C09947A5A104@microsoft.com...
>> 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" <Kathleen@discussions.microsoft.com> wrote in message
>>> news:B913757F-8894-4C05-86B9-F0962F1C989B@microsoft.com...
>>> > 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" <Kathleen@discussions.microsoft.com> wrote in message
>>> >> news:B06C536D-1802-48F6-9F57-F3C9636FF65A@microsoft.com...
>>> >> > 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" <Kathleen@discussions.microsoft.com> wrote in message
>>> >> >> news:D125DA8D-24B4-4BEA-BCD6-AB3D1C0312EC@microsoft.com...
>>> >> >> > 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
>>> >> >>
>>> >> >> .
>>> >> >>
>>> >>
>>> >> .
>>> >>
>>>
>>> .
>>>
> 

0
Fred
2/24/2010 5:27:39 PM
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 
> news:WMKdnThahNrFfBnWnZ2dnUVZ8sadnZ2d@bt.com...
> > 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" <Kathleen@discussions.microsoft.com> wrote in message 
> > news:4B6A7B86-E4E8-4011-A68C-C09947A5A104@microsoft.com...
> >> 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" <Kathleen@discussions.microsoft.com> wrote in message
> >>> news:B913757F-8894-4C05-86B9-F0962F1C989B@microsoft.com...
> >>> > 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" <Kathleen@discussions.microsoft.com> wrote in message
> >>> >> news:B06C536D-1802-48F6-9F57-F3C9636FF65A@microsoft.com...
> >>> >> > 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" <Kathleen@discussions.microsoft.com> wrote in message
> >>> >> >> news:D125DA8D-24B4-4BEA-BCD6-AB3D1C0312EC@microsoft.com...
> >>> >> >> > 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
> >>> >> >>
> >>> >> >> .
> >>> >> >>
> >>> >>
> >>> >> .
> >>> >>
> >>>
> >>> .
> >>>
> > 
> 
> .
> 
0
Utf
3/1/2010 10:56:04 PM
You're welcome. Thanks for the feedback.

Regards,
Fred

"Kathleen" <Kathleen@discussions.microsoft.com> wrote in message 
news:935331E5-B84A-4742-B67D-EF35857B02E6@microsoft.com...
> 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
>> news:WMKdnThahNrFfBnWnZ2dnUVZ8sadnZ2d@bt.com...
>> > 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" <Kathleen@discussions.microsoft.com> wrote in message
>> > news:4B6A7B86-E4E8-4011-A68C-C09947A5A104@microsoft.com...
>> >> 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" <Kathleen@discussions.microsoft.com> wrote in message
>> >>> news:B913757F-8894-4C05-86B9-F0962F1C989B@microsoft.com...
>> >>> > 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" <Kathleen@discussions.microsoft.com> wrote in message
>> >>> >> news:B06C536D-1802-48F6-9F57-F3C9636FF65A@microsoft.com...
>> >>> >> > 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" <Kathleen@discussions.microsoft.com> wrote in 
>> >>> >> >> message
>> >>> >> >> news:D125DA8D-24B4-4BEA-BCD6-AB3D1C0312EC@microsoft.com...
>> >>> >> >> > 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
>> >>> >> >>
>> >>> >> >> .
>> >>> >> >>
>> >>> >>
>> >>> >> .
>> >>> >>
>> >>>
>> >>> .
>> >>>
>> >
>>
>> .
>> 

0
Fred
3/2/2010 12:34:41 AM
Reply:

Similar Artilces:

Multiple lookup between two dates
Hi, I have a ratesheet in excel like e.g. Carrier FromDate ToDate Rate A 01.02.10. 28.02.10. 100 A 01.01.10. 14.01.10. 99 A 15.01.10. 31.01.10. 98 B 13.01.10. 31.10.10. 101 C 16.01.10. 21.02.10. 97 A 13.12.09. 28.12.09. 101 etc. The date for respective carrier are not overlapping. So in the rate sheet will never happen, that a rate is valid for carrier A fm 01.01.10. till 31.01.10. and another rate for the same carrier A fm 15.01.10. till 31.01.10. In such case the previous one will be valid jus...

Formula request
I need to find a formula that will tell me the frequency of a character appearing within a range of text. For instance, A1 has JUICY and A2 has JUMBO. If I want to find the number of times J appears, it's 2 times Thanks you. One way: =SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"J",""))) (watch out =substitute is case sensitive, so it won't count lower case j's. If you want to count both j's and J's: =SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"J",""))) Joe wrote: > > I need to find a formula that will te...

Formula in Table
Not sure if im posting this in the right category; if not apologies... Ive created a pretty straight-forward table to capture training. So one of those fields is "Seats Available" with a number such as 25. Another field is Seats Booked - lets say that value is 20... I want a 3rd field: Seats Left to calculate automatically :) so i thought i could do that when designing the table but i cant see any wayn of doing it... I know it can be done if i pull it into a query On Jan 3, 2:12=A0pm, ant1983 <ant1...@discussions.microsoft.com> wrote: > Not sure if im po...

change a formula dynamically
Hi all, I'm experiencing a tough MS excel function problem. Please give me some advices. ------------------------------------- a b c d 1 3 9 50 2 10 8 40 3 7 30 4 6 20 5 5 10 .. . . .. . . .. . . ------------------------------------- I need to put my results in column D. Currently, D1 should euqal to "if(C1-SUM(B1:B3)>C3,C1-B1,C1-B1+$A$2)". That means I need to make a justification that whether C1 is bigger than the summary of B1 to B3. If it's smaller than the sum, I need to add a constant($A$2) to D1 while reducing B1. Then D2=if(C2-SUM(B2:B4)>C4,C2-B2,C2-B2...

Nested if's limit and Named formulae
This is the second time trying to get an answer, with more detail than last time, so here's hoping I have a cost estimate spreadsheet, comprising ten or so worksheets, the first one of which (Details) is a summary worksheet with a changes log at the bottom of it. This log contains the values Draft1-10 in column B, the Date of change for each in column C, the worksheet affected in column D and the nature of the change in column E. Further up the worksheet is a field that I want to populate to show the current version. I can pick this up by checking to see whether there is a date in colu...

Determining number of dates held
Hi, I am using Excel 2002. In column A I have a list of dates I purchased an item. In column B I have the date it was sold. How can I automatically calculate, in C, the number of days held? I case it matters, the date format is dd/mm/yyyy thanks Daniel Sure, in C2, put something like: =if(isblank(b2),"",b2-a2) Copy it down. Make sure C is formatted as numbers and not dates. ************ Anne Troy www.OfficeArticles.com "Daniel" <none@none> wrote in message news:%23VfjqAYvFHA.908@tk2msftngp13.phx.gbl... > Hi, > > I am using Excel 2002. > In colum...

Date/time stamp for entries in a contact's notes Outlook 2002
Does anyone know if Outlook has the ability to date/time stamp entries in a contact's notes? When taking notes in Outlook on topics/things I discuss with my customers I want to make sure that they are date/time stamped & therefore admissible into a court of law. Any ideas & insight anyone can lend me is greatly appreciated. Thanks, Matt holbrook@gmx.net http://www.poremsky.com/DateStamp.htm Matt wrote: > Does anyone know if Outlook has the ability to date/time > stamp entries in a contact's notes? When taking notes in > Outlook on topics/things I discuss...

automatic date
Is there anyway that a specific date shall be entered automatically for example the computer shall search in the excel table if there is already a date as the 10 of the current month if not it shall input the date in a date field a cell each 10 th of the month after it past or on it ...

Evaluate File for Last Updated Date
I want to have my macro code determine the date a file was last updated and then from there, decide whether or not to open it. What I don't know is how to access the file's property that returns that date it was updated. If I could just use an "If" statement to say something like this, in principle: If Filename.DateUpdated >= Today() - 1 Then Workbooks.Open Filename End If The goal is to only open files that have been updated within the last two days. Is this possible? Using document properties. IF Filename.BuiltinDocumentProperties("Last S...

How to detect system date change in MFC
Hi, I am developing a secruity surveillance application, where i am recording videos from a CCTV camera. I have to record the videos in that particular days directory in date tree format. The thing is when the system date changes to a different date i should be able to detect that and start recording the videos in a different directory. I know i can use the settimer and ontimer functions, but say the settimer is called at 23.30 and the duration is set to 1 hr then the ontimer event will fire at 00.30 am its already half an hour into the next day. Is there any inherent way of detecting the sys...

Formulas #36
Hi! What formula would I use to calculate an n/a value. For example, A1-A7 have varying numerical values, but 8 and 9 are n/a. What formula would I use? Thanks in advance for your help. In my test, having the text "N/A" didn't bother a sum of a column including that text. If you mean, the error value #N/A as the result of a formula, I usually suppress them from appearing on the sheet with IF(ISERROR(some calculation),"",some calculation) for that cell. You can also use ISNA if you specifically want to trap them. ISERROR gets all of the formula errors. It doubles...

Extracting Part of a Date
Hi, I have a variable in VBA called myDate and I need to extract the fist two letter of the day, from the date which is stored in myDate. For example, if myDate = Tuesday, July 3 2007, then I need to put 'TU' into variable of myDay. Can anyone help? Thanks. Maybe this might help (extracted from VBA Help: Left Function Example This example uses the Left function to return a specified number of characters from the left side of a string. Dim AnyString, MyStr AnyString = "Hello World" ' Define string. MyStr = Left(AnyString, 1) ' Returns "H". MySt...

Copying Formulas In Pivot Table
Working in a pivot table where I have formulas pulling from other fields within the pivot table, can I copy the formula to range of cells without having to enter the formula in each cell? I keep getting an error that I cannot copy/paste or drag the formula across the table. Thanks! ...

In a formula, how do I set a maximum acceptable value?
I need to calculate my loss on a rental property but the loss cannot be greater than $25,000 no matter how large it actually is. How do I write an Excel formula that will include this maximum value limit? Use the Min function, as in: =min(25000,yourformula) Regards, Fred "hollisterbulldawg" <hollisterbulldawg@discussions.microsoft.com> wrote in message news:E8CCDA8F-F60E-4D48-BDE8-8B35760E2BD3@microsoft.com... >I need to calculate my loss on a rental property but the loss cannot be > greater than $25,000 no matter how large it actually is. How do I write...

Total number of days between 2 dates
Just need to be able to input a date in column A and B then get it to calculate total days in column C across the columns and at the bottom of the column On Mon, 17 Mar 2008 05:00:02 -0700, guillottes <guillottes@discussions.microsoft.com> wrote: >Just need to be able to input a date in column A and B then get it to >calculate total days in column C across the columns and at the bottom of the >column C: =B-A Format as General or as Number with zero decimals --ron ...

date cell format
i am working with an external data set (european) in which the date cell is formatted as this: dd.mm.yy when i sort by date, excel does not recognize the format and thus the initial sort is by day instead of by month. is there a way to fix this or get around it so i can sort by date and the result will be sequential? thanks! acb I'm not sure what's happening for you...but I have 10 cells with custom format of dd.mm.yy When I sort them, they do sor sequentially...I'm using Windows 2000 and Office XP. Wish I could be more of a help. Good luck, Dave M -- Message posted from http...

How can you print formulas in Excel 2000?
Hi Try this http://www.rondebruin.nl/print.htm#Formulas -- Regards Ron de Bruin http://www.rondebruin.nl "tdshort" <tdshort@discussions.microsoft.com> wrote in message news:EEEE9420-4DF6-4E03-ADA2-BD46D19CF4F4@microsoft.com... > tdshort, one way ctrl+` to toggle between formulas and values -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 2003 ** remove news from my email address to reply by email ** "tdsho...

Number of Days in a Date Range
I am creating a spread sheet to identify the number of days a patient was on a particular ward. sometimes, the patient is a resident for a span of time that includes several different months. What I need a formula that will break down the number of days the patient was in admittance for each different month: Example: John Doe 1/5/05 through 3/7/05 What is required is to calculate the number of days for each month, separately. Jan Feb Mar ? ? ? This can be facilitated if you use actual dates for the Jan Feb Mar headers (format them as mmm-yy). The answer wil...

Chart axis custom date format
Dear all! Excel 2003, charting. On Y axis I have scale from 0 to 7, and when I go to "format axis- number-custom" I can input formula "[Blue][<4]#.###,0;[Red][>4]#.###, 0;#.###,0" which colors the scale as I would like. On X axis I have dates (1.jan.06, 1.feb.06, 1.mar.06....1.jun.08). Is it possible to write similar formula as above on x axis - for instance if date is less than 1.jan.07 then color it red, if less than 1.jan 08 than color it blue? This would allow to have on X axis only date format "mmm" and the colors would help differentiate between yea...

US Date Formatting
Folks I have been formatting my query dates to the US format as I live in Australia. Does the Date() function require similar formatting when used in a query Where clause? Thanks Tom Tom wrote: >I have been formatting my query dates to the US format as I live in >Australia. > >Does the Date() function require similar formatting when used in a >query Where clause? > No, not unless you are concatenating it into a longer string. The reason it that the date function returns a date/time type **value** Note that you can use a more international standa...

Help with formula #25
Original formula from previous post... =(("12:00"-MAX(C11,--"7:00"))*0.3+(MAX("23:00",C11)-MAX(C11,"12:00"))*0.4+(C12-MIN(C12,"23:00"))*0.5)*24 I have changed the formula to =(("12:00"-MAX(C11,--"7:00"))*0.3+(MAX("23:00",C11)-MAX(C11,"12:00"))*0.4+(C12-MIN(C12,"23:00"))*0.5)*C13 As a test I have put in aircon running for 1 hour between 10-11 am So this should return a value of .30 It is however returning a value of 2.17 Can anyone help with this. Thanks Original post..... >...

This is a date question...
Here is what I am trying to do. I have two columns. The values in the first column, let's call them Begin Date. The values in the second column we will call End Date. What I am trying to do is creat (in a third column) a Yes or No value based on the following criteria: If (amount of time) between Begin Date and End Date is less than 1 week, put a value of Yes in the third column for this record. If not, put a No. Can this be done? -- RSF One way: XL stores dates as long integer offsets from a base date, so you can use regular math on them: =IF(end_date - begin_dat...

formula to look up ref in one sheet and offset in another
Hi there I am trying to create a formula which will use a product code typed into a cell in the same sheet, look up this code in a separate file, and give me the sales figure which is offset from this code. I.e. (if this helps) This is the formula I have at the moment just using the other file: =OFFSET('[Budget Tracker Master Copy 2010.xlsm]Total by Product'!$EJ$5,739,3) but I want the code which is in EJ5 to come from my current sheet which is in cell B8. The range to look up in the Budget Tracker sheet would be $B$5:$MS$5 Hope this makes sense! Thanks =offset() i...

Crosstab By Date (Month)
I need to write a crosstab on a shipping table that has the Customer as the row heading, month as the column heading, and sum of shipped as the value. The crosstab wizard does this easy enough, but my problem is I need to have the column heads (month) sort by year also. If I use the wizard and run a crosstab on the last 6 months, it still sorts the colomns as Jan, Feb, Mar,.......Dec. What I am going for is ....Sept 09, Oct 09, Nov09, Dec09, Jan10, Feb10. The data is correct the first way - just sorted wrong. Is there a way to do this? Thanks Terry In the SQL view for...

Place Date in One column when entry is made in another column.
Column J cells of my spreadsheet will contain cash contributions made by certain individuals. When a dollar value is entered in cell J5, I would like to have the date of the entry automatically entered in cell k5. Am unable to figure our a function to handle this. Any help is appreciated. JT See this: http://www.mcgimpsey.com/excel/timestamp.html -- Biff Microsoft Excel MVP "JT" <JT@discussions.microsoft.com> wrote in message news:1D77BB32-6BEC-42C3-8950-6EB85EAE5DE9@microsoft.com... > Column J cells of my spreadsheet will contain cash contribu...