|
date calculations
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. |
On Mon, 10 Oct 2005 04:49:01 -0700, TUNGANA KURMA RAJU
wrote: 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) 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. --ron |
"TUNGANA KURMA RAJU" wrote in message ... 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 1-NOV-2008) |
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. "Ron Rosenfeld" wrote: On Mon, 10 Oct 2005 04:49:01 -0700, TUNGANA KURMA RAJU wrote: 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) 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. --ron |
TUNGANA KURMA RAJU Wrote: 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. "Ron Rosenfeld" wrote: On Mon, 10 Oct 2005 04:49:01 -0700, TUNGANA KURMA RAJU wrote: 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) 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. --ron Hi To get the ordinal use this formula =DATEDIF(A1,B1,"y"), you will need analysis pak on for this to work -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=474720 |
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"))) -- HTH Bob Phillips "TUNGANA KURMA RAJU" wrote in message ... 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. "Ron Rosenfeld" wrote: On Mon, 10 Oct 2005 04:49:01 -0700, TUNGANA KURMA RAJU wrote: 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) 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. --ron |
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 TUNGANA KURMA RAJU wrote: 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. "Ron Rosenfeld" wrote: On Mon, 10 Oct 2005 04:49:01 -0700, TUNGANA KURMA RAJU wrote: 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) 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. --ron |
Because it is his 30th birthday! :-)
Bob "Roger Govier" wrote in message ... 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 TUNGANA KURMA RAJU wrote: 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. "Ron Rosenfeld" wrote: On Mon, 10 Oct 2005 04:49:01 -0700, TUNGANA KURMA RAJU wrote: 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) 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. --ron |
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 Bob Phillips wrote: Because it is his 30th birthday! :-) Bob "Roger Govier" wrote in message . .. 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 TUNGANA KURMA RAJU wrote: 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. "Ron Rosenfeld" wrote: On Mon, 10 Oct 2005 04:49:01 -0700, TUNGANA KURMA RAJU wrote: 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) 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. --ron |
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 "Roger Govier" wrote in message ... 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 Bob Phillips wrote: Because it is his 30th birthday! :-) Bob "Roger Govier" wrote in message . .. 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 TUNGANA KURMA RAJU wrote: 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. "Ron Rosenfeld" wrote: On Mon, 10 Oct 2005 04:49:01 -0700, TUNGANA KURMA RAJU wrote: 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) 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. --ron |
On Mon, 10 Oct 2005 23:56:03 -0700, TUNGANA KURMA RAJU
wrote: 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. 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 |
On Tue, 11 Oct 2005 11:05:29 +0100, "Bob Phillips"
wrote: 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 As did I! --ron |
| All times are GMT +1. The time now is 09:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com