birthdays list

[KlausW]

Hi
I want to make a birthday list where the months come in a cell for themselves and the birthdays come down there after. Starting with January. The challenges are that new people come every year so the birthdays are not the same year after year. Someone who can help.
The red are my formulas and they are hidden
The dates are in danish dd/mm/yy
The file is uploadet to Dropbox Birthday
All help will be appreciated
Regards Klaus W

 

[ebea]

Is your goal, to have a list as you show in tab "Final result"? If so, what are the challenge? Is it because there comes new people, and you need space in the list, or?
It's a bit difficult out from your uploaded example, to see what you actually want. So try to explain a bit more in detail.

Din forklaring i "Final result" som du har skrevet i selve din uploadede fil, gir ikke meget info!

 

[KlausW]

Hi Ebea, Should I try to explain it to you in Danish?

 

[KlausW]

Is your goal, to have a list as you show in tab "Final result"? If so, what are the challenge? Is it because there comes new people, and you need space in the list, or?
It's a bit difficult out from your uploaded example, to see what you actually want. So try to explain a bit more in detail.

Din forklaring i "Final result" som du har skrevet i selve din uploadede fil, gir ikke meget info!

I fanebladet Birthday list star dette års personales fødselsdage. Kolonner skulle gerne give sig selv.
Fra Fanebladet Name kommer CPR nr. fra kolonne A. Det jeg gerne vil have Excel til er, at dele fødselsdagene op efter måneder, så månederne kommer til at stå som i Faneblad Final result. I Række 2, 5 og 8 stå månederne januar, februar og marts, til december. Også skal fødselsdagene stå nedenunder som vist i Fanebladet Final result.
Det svære er at personalet skifter hvert år og derfor er det ikke de samme fødselsdage.
Er det nemere at forstå, så kan jeg bare oversætte det ? KW

 

[KlausW]

Is it because new people are coming, and I need space on the list, for the new birthdays.

 

[KlausW]

Is it because new people are coming, and I need space on the list, for the new birthdays.

 

[Dave Patton]

Quote KW " Good morning Dave I do not know how to for Excel to what I can not. Could you grab the file and open it from Dropbox? " ???

My example starts with your information.
You can copy the post to Excel. See the icon below f(x)
a) I show changes for January - March. You can use the same ideas for the full twelve months.
b) insert rows
c) add date in H2 =DATE(1,1,1) and formulas in H3:4 =EDATE(H2,1)
d) A2:A4 are centered across columns A:D. This does not show on the post.

T202009c.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Dato	Dag	Navn	Skibs nr.	Alder	31-12-20	CPR	DOB	MDR.	DOB2	Month
2	JANUARY							1-Jan-01	1	01-Jan	Jan
3	FEBRUARY							1-Feb-01	2	01-Feb	Feb
4	MARCH							1-Mar-01	3	01-Mar	Mar
5	18		John		29	januar	200158-1111	20-Jan-58	1	20-Jan	Jan
6	20		Ole		62	februar	040272-1111	4-Feb-72	2	04-Feb	Feb
7	04		Anne		48	marts	040392-1111	4-Mar-92	3	04-Mar	Mar
8			XXX			marts	250300-1111	25-Mar-00	3	25-Mar	Mar
9	14		Dorte		32	marts	290375-1111	29-Mar-75	3	29-Mar	Mar
10
2e

Cell Formulas
Range	Formula
H2	H2	=DATE(1,1,1)
I2:I9	I2	=MONTH(H2)
J2:J9	J2	=DATE(1,MONTH(H2),DAY(H2))
K2:K9	K2	=TEXT(H2,"mmm")
H3:H4	H3	=EDATE(H2,1)
H5	H5	=IF(G5="",DATE(1,1,1),--TEXT(LEFT(G5,6),"00-00-00"))
H6:H9	H6	=--TEXT(LEFT(G6,6),"00-00-00")
A2:A4	A2	=UPPER(TEXT(H2,"mmmm"))

1. Convert Column H to values
2. Sort the entire table by Column DOB2

T202009c.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Dato	Dag	Navn	Skibs nr.	Alder	Month	CPR	DOB	MDR.	DOB2	Month
2	JANUARY							1-Jan-01	1	01-Jan	Jan
3	18		John		29	januar	200158-1111	20-Jan-58	1	20-Jan	Jan
4	FEBRUARY							1-Feb-01	2	01-Feb	Feb
5	20		Ole		62	februar	040272-1111	4-Feb-72	2	04-Feb	Feb
6	MARCH							1-Mar-01	3	01-Mar	Mar
7	04		Anne		48	marts	040392-1111	4-Mar-92	3	04-Mar	Mar
8			XXX			marts	250300-1111	25-Mar-00	3	25-Mar	Mar
9	14		Dorte		32	marts	290375-1111	29-Mar-75	3	29-Mar	Mar
10
2ee

Cell Formulas
Range	Formula
A2,A6,A4	A2	=UPPER(TEXT(H2,"mmmm"))
I2:I9	I2	=MONTH(H2)
J2:J9	J2	=DATE(1,MONTH(H2),DAY(H2))
K2:K9	K2	=TEXT(H2,"mmm")

With your file, you would have the Labels and DOB2 with Dates like Jan 1 01 to Dec 1 01
Add additional employees at the bottom
Delete any rows that are not needed

Sort the entire table on DOB2

 

[ebea]

I fanebladet Birthday list star dette års personales fødselsdage. Kolonner skulle gerne give sig selv.
Fra Fanebladet Name kommer CPR nr. fra kolonne A. Det jeg gerne vil have Excel til er, at dele fødselsdagene op efter måneder, så månederne kommer til at stå som i Faneblad Final result. I Række 2, 5 og 8 stå månederne januar, februar og marts, til december. Også skal fødselsdagene stå nedenunder som vist i Fanebladet Final result.
Det svære er at personalet skifter hvert år og derfor er det ikke de samme fødselsdage.
Er det nemere at forstå, så kan jeg bare oversætte det ? KW

All this you explain above, I understand. But this I ask about, are if you want to add more people to the list which are on Tab "Name", and these more names, has to be reflected on the Tab "Final result". And by this I mean, that it could be, that you suddenly has 5 persons with Birthsday i January, and by this you need to could add rows for these extra persons.
As now, you have fixed rows!

 

[KlausW]

Good evening Ebea, it's just right if I add more people with different birthdays, they should be added in the Name tab, they should be reflected in the Final Result tab, so one year there may well be 4 in January and next year 2. So there should be add more rows. they must be flexible. Your explanation is exactly what I'm looking for. Regards Klaus W

 

[ebea]

I have made a new proposal, based on your name Tab, for inspiration (change as you want). You just add new persons on the list, by adding the persons CPR nr. in Column A, and then all the rest of info will be updated automatic by formulas.
And you can see a Button on the Sheet, which you can use to sort the list when ever you want. It will now sort by days, up to fort coming Birthday for each person.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[KlausW]

Hi Ebea thanks I look at it. Good weekend. KW