date calculations

G

Guest

I am working on a personal worksheet.In cell A1 I put my Date of
birth(1-NOV-1975) in ddmmmyyyy format.What formula I need to write to get(in
B1 to B6)
1.What date will be my next birth day?(output:1-NOV-2005)
2.What day it will be?(output:Tuesday)
3.How many days left for my upcoming birth day?(output:21 days)
4.Whether today is my birth day or not?(output:no)
5.What will be my upcoming birth day's ordinal?(output:30th)
6.Date of my earliest upcoming birth day that falls on sunday?(say 1-NOV-2008)
For me I tried with date formulas.some gave output errors ,numbers.
office xp version,widows me.
 
R

Ron Rosenfeld

I am working on a personal worksheet.In cell A1 I put my Date of
birth(1-NOV-1975) in ddmmmyyyy format.What formula I need to write to get(in
B1 to B6)
1.What date will be my next birth day?(output:1-NOV-2005)
"=DATE(YEAR(TODAY())+(DATE(YEAR(TODAY()),
MONTH(A1),DAY(A1))<=TODAY()),MONTH(A1),DAY(A1))"

2.What day it will be?(output:Tuesday)
=TEXT(B1,"dddd")

3.How many days left for my upcoming birth day?(output:21 days)
=B1-TODAY()

4.Whether today is my birth day or not?(output:no)
=IF(B1=TODAY(),"Yes","No")

5.What will be my upcoming birth day's ordinal?(output:30th)
=DAY(B1)&IF(OR(DAY(B1)={1,21,31}),"st",
IF(OR(DAY(B1)={2,22}),"nd",IF(OR(
DAY(B1)={3,23}),"rd","th")))

6.Date of my earliest upcoming birth day that falls on sunday?(say 1-NOV-2008)
Click to expand...

This is an array formula. After typing or pasting it in, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula.

=DATE(YEAR(B1)+MATCH(TRUE,WEEKDAY(DATE(
YEAR(TODAY())+(DATE(YEAR(TODAY()),MONTH(
A1),DAY(A1))<=TODAY())+ROW(INDIRECT(""1:10"")),
MONTH(A1),DAY(A1)))=1,0),MONTH(B1),DAY(B1))
For me I tried with date formulas.some gave output errors ,numbers.
office xp version,widows me.
Click to expand...


--ron
 
B

Bob Phillips

TUNGANA KURMA RAJU said:
I am working on a personal worksheet.In cell A1 I put my Date of
birth(1-NOV-1975) in ddmmmyyyy format.What formula I need to write to get(in
B1 to B6)
1.What date will be my next birth day?(output:1-NOV-2005)
=DATE(YEAR(TODAY())+(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1
),DAY(A1))

2.What day it will be?(output:Tuesday)
=TEXT(B1,"dddd")

3.How many days left for my upcoming birth day?(output:21 days)
=B1-TODAY()

4.Whether today is my birth day or not?(output:no)

5.What will be my upcoming birth day's ordinal?(output:30th)
=IF(B1=TODAY(),"","no")

6.Date of my earliest upcoming birth day that falls on sunday?(say
Click to expand...
1-NOV-2008)
 
G

Guest

A miillion thanks to Mr.Ron Rosenfeld.Its a great job you have done.Actually
my question(5)is wrong,I want my upcoming birth day's(age) ordinal.All other
formulas working fine.Iam analysing the logic of answer 6.What a great
logic.Thanks once again.
 
P

Paul Sheppard

TUNGANA said:
A miillion thanks to Mr.Ron Rosenfeld.Its a great job you have
done.Actually
my question(5)is wrong,I want my upcoming birth day's(age) ordinal.All
other
formulas working fine.Iam analysing the logic of answer 6.What a great
logic.Thanks once again.
Click to expand...

Hi

To get the ordinal use this formula > =DATEDIF(A1,B1,"y"), you will
need analysis pak on for this to work
 
B

Bob Phillips

Try this instead

=YEAR(B1)-YEAR(A1)&IF(OR(YEAR(B1)-YEAR(A1)={1,21,31,41,51,61,71,82,91}),"st"
,
IF(RIGHT(YEAR(B1)-YEAR(A1),1)="2","nd",IF(RIGHT(YEAR(B1)-YEAR(A1),1)="3","rd
","th")))
 
R

Roger Govier

Hi

The ordinal of every Birthday will be the same, so your upcoming
Birthday's ordinal is the same as your date of birth.
You gave that in your original posting as 01-NOV-1975 and Ron's formula
will rightly give 1st as the result.
In your posting, you seemed to expect 30th as the result, but I cannot
see why.

Regards

Roger Govier
 
B

Bob Phillips

Because it is his 30th birthday! :)

Bob

Roger Govier said:
Hi

The ordinal of every Birthday will be the same, so your upcoming
Birthday's ordinal is the same as your date of birth.
You gave that in your original posting as 01-NOV-1975 and Ron's formula
will rightly give 1st as the result.
In your posting, you seemed to expect 30th as the result, but I cannot
see why.

Regards

Roger Govier
Click to expand...
 
R

Roger Govier

Hi Bob

Doh!!!
Upcoming age ordinal, which I read as Birthday's ordinal.
Glad the rest of you are awake, now where's that coffee pot????

Regards

Roger Govier
 
B

Bob Phillips

Hi Roger,

Don't know what you are worried about, I missed that part off completely in
my first response. Perhaps I need the whisky pot :).

Bob
 
R

Ron Rosenfeld

A miillion thanks to Mr.Ron Rosenfeld.Its a great job you have done.Actually
my question(5)is wrong,I want my upcoming birth day's(age) ordinal.All other
formulas working fine.Iam analysing the logic of answer 6.What a great
logic.Thanks once again.
Click to expand...

You're welcome.

With regard to the ordinal, once you understand the logic of the answers, you
should be able to alter it to generate age ordinal's. Merely expand the OR
functions to include the appropriate numbers for each type of ordinal.


--ron
 
R

Ron Rosenfeld

Hi Roger,

Don't know what you are worried about, I missed that part off completely in
my first response. Perhaps I need the whisky pot :).

Bob
Click to expand...

As did I!
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is this a bug in Excel 2007? 1
Date Formula 85 factor 7
Formula to indicate upcoming birthdays 5
formual to determine if date falls on weekend, adjust date to Mond 4
Test if a date falls within a given range of dates in Excel? 6
Calculate End Date Using Start Date and No. of Days excluding specific days. 0
Require a Formula to caluclate the date 1
Count down to a date 3

Top