Day of the week

F

Francis Hookam

Day of the week

I know that A was born on 28/12/1933 and B was born on 18/5/1937 ­ there are
75 people in the spreadsheet and I need see at a glance which birthdays fall
on a Tuesday during the next twelve months automatically updating using
today¹s date on opening

A 18/5/37 18/5/05
B 4/10/19 4/10/05 Tuesday
C 27/6/57 27/6/05
D 29/2/40 1/3/05 Tuesday*
E 8/6/43 8/6/05
F 27/4/44 28/4/05
G 28/12/56 28/12/04 Tuesday

*Not sure what to do with D who was born on Leap Year Day!

I just cannot see how to generate 18 May which falls in the next 12 months
automatically in the second cell

I tried calculating how old the person is in years using
=INT((TODAY()-BirthDate)/365.25)
and adding that to the birth date and then using
=RC[-2]+Years*365.25
to find the date BUT SOMETIMES IT IS A DAY OUT
then an easy =IF(Weekday(...) = 3),"Tuesday","") to show Tuesdays in the
next column

Any suggestions

Thanks as always

Francis Hookham
 
P

Peo Sjoblom

To get the age in years

=DATEDIF(A1,TODAY(),"y")

for more info see

http://www.cpearson.com/excel/datedif.htm


to get the weekdays of the birthdays coming 12 months

=TEXT(IF(AND(DATE(YEAR(TODAY()),12,31)>=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),TODAY()<=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1))),"dddd")


copied down

I am sure there are smarter ways but it seems to work, btw I used March 1
for birthdays on Feb 29 on non leap years
The above will return all weekdays not only Tuesdays


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
P

Peo Sjoblom

If you want to use your if formula testing for Tuesday you can use

=IF(WEEKDAY(IF(AND(DATE(YEAR(TODAY()),12,31)>=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),TODAY()<=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1))))=3,"Tuesday","")

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



Peo Sjoblom said:
To get the age in years

=DATEDIF(A1,TODAY(),"y")

for more info see

http://www.cpearson.com/excel/datedif.htm


to get the weekdays of the birthdays coming 12 months

=TEXT(IF(AND(DATE(YEAR(TODAY()),12,31)>=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),TODAY()<=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1))),"dddd")


copied down

I am sure there are smarter ways but it seems to work, btw I used March 1
for birthdays on Feb 29 on non leap years
The above will return all weekdays not only Tuesdays


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



Francis Hookam said:
Day of the week

I know that A was born on 28/12/1933 and B was born on 18/5/1937 ­ there
are
75 people in the spreadsheet and I need see at a glance which birthdays
fall
on a Tuesday during the next twelve months automatically updating using
today¹s date on opening

A 18/5/37 18/5/05
B 4/10/19 4/10/05 Tuesday
C 27/6/57 27/6/05
D 29/2/40 1/3/05 Tuesday*
E 8/6/43 8/6/05
F 27/4/44 28/4/05
G 28/12/56 28/12/04 Tuesday

*Not sure what to do with D who was born on Leap Year Day!

I just cannot see how to generate 18 May which falls in the next 12
months
automatically in the second cell

I tried calculating how old the person is in years using
=INT((TODAY()-BirthDate)/365.25)
and adding that to the birth date and then using
=RC[-2]+Years*365.25
to find the date BUT SOMETIMES IT IS A DAY OUT
then an easy =IF(Weekday(...) = 3),"Tuesday","") to show Tuesdays in the
next column

Any suggestions

Thanks as always

Francis Hookham
Click to expand...
Click to expand...
 
F

Francis Hookam

Brilliant! - many thanks Peo

I must sit an analyse it

I ended up with two calculations (I'm an R1C1 person!):

=TEXT(IF(AND(DATE(YEAR(TODAY()),12,31)>=DATE(YEAR(TODAY()),MONTH(RC2),DAY(RC
2)),TODAY()<=DATE(YEAR(TODAY()),MONTH(RC2),DAY(RC2))),DATE(YEAR(TODAY()),MON
TH(RC2),DAY(RC2)),DATE(YEAR(TODAY())+1,MONTH(RC2),DAY(RC2))),"ddd d mmm yy")

and

=IF(WEEKDAY(IF(AND(DATE(YEAR(TODAY()),12,31)>=DATE(YEAR(TODAY()),MONTH(RC2),
DAY(RC2)),TODAY()<=DATE(YEAR(TODAY()),MONTH(RC2),DAY(RC2))),DATE(YEAR(TODAY(
)),MONTH(RC2),DAY(RC2)),DATE(YEAR(TODAY())+1,MONTH(RC2),DAY(RC2))))=3,"Tuesd
ay","")

===============================================
Day of the week

I know that A was born on 28/12/1933 and B was born on 18/5/1937 ­ there
are
75 people in the spreadsheet and I need see at a glance which birthdays
fall
on a Tuesday during the next twelve months automatically updating using
today¹s date on opening

A 18/5/37 18/5/05
B 4/10/19 4/10/05 Tuesday
C 27/6/57 27/6/05
D 29/2/40 1/3/05 Tuesday*
E 8/6/43 8/6/05
F 27/4/44 28/4/05
G 28/12/56 28/12/04 Tuesday

*Not sure what to do with D who was born on Leap Year Day!

I just cannot see how to generate 18 May which falls in the next 12
months
automatically in the second cell

I tried calculating how old the person is in years using
=INT((TODAY()-BirthDate)/365.25)
and adding that to the birth date and then using
=RC[-2]+Years*365.25
to find the date BUT SOMETIMES IT IS A DAY OUT
then an easy =IF(Weekday(...) = 3),"Tuesday","") to show Tuesdays in the
next column

Any suggestions

Thanks as always

Francis Hookham
Click to expand...
Click to expand...
 
P

Peo Sjoblom

My Pleasure

Regards,

Peo Sjoblom


Francis Hookam said:
Brilliant! - many thanks Peo

I must sit an analyse it

I ended up with two calculations (I'm an R1C1 person!):

=TEXT(IF(AND(DATE(YEAR(TODAY()),12,31)>=DATE(YEAR(TODAY()),MONTH(RC2),DAY(RC
2)),TODAY()<=DATE(YEAR(TODAY()),MONTH(RC2),DAY(RC2))),DATE(YEAR(TODAY()),MON
TH(RC2),DAY(RC2)),DATE(YEAR(TODAY())+1,MONTH(RC2),DAY(RC2))),"ddd d mmm yy")

and

=IF(WEEKDAY(IF(AND(DATE(YEAR(TODAY()),12,31)>=DATE(YEAR(TODAY()),MONTH(RC2),
DAY(RC2)),TODAY()<=DATE(YEAR(TODAY()),MONTH(RC2),DAY(RC2))),DATE(YEAR(TODAY(
)),MONTH(RC2),DAY(RC2)),DATE(YEAR(TODAY())+1,MONTH(RC2),DAY(RC2))))=3,"Tuesd
ay","")

===============================================
Day of the week

I know that A was born on 28/12/1933 and B was born on 18/5/1937 ­ there
are
75 people in the spreadsheet and I need see at a glance which birthdays
fall
on a Tuesday during the next twelve months automatically updating using
today¹s date on opening

A 18/5/37 18/5/05
B 4/10/19 4/10/05 Tuesday
C 27/6/57 27/6/05
D 29/2/40 1/3/05 Tuesday*
E 8/6/43 8/6/05
F 27/4/44 28/4/05
G 28/12/56 28/12/04 Tuesday

*Not sure what to do with D who was born on Leap Year Day!

I just cannot see how to generate 18 May which falls in the next 12
months
automatically in the second cell

I tried calculating how old the person is in years using
=INT((TODAY()-BirthDate)/365.25)
and adding that to the birth date and then using
=RC[-2]+Years*365.25
to find the date BUT SOMETIMES IT IS A DAY OUT
then an easy =IF(Weekday(...) = 3),"Tuesday","") to show Tuesdays in the
next column

Any suggestions

Thanks as always

Francis Hookham
Click to expand...
Click to expand...
Click to expand...
 

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

vlookup help? 8
Suming Up Time over the course of days 1
Find then highlight in yellow 6
Help!! find two number recurring numbers in row?? 1
Countif Function?? 3
Search closest day in the past from today 2
Calculating interest due on late loan payments 1
Count the remaining weekdays in a data range excluding holidays and start/end dates 5

Top