2 questions regarding Dates
 

Q1
I have inherited a spreadsheet that has the date split over 3 columns: -
a1 = day
b1 = month
c1 = year

How can I put these in one column to read dd/mm/yyy

Q2

How can I work out the day number of a particular date?

Ie 28/10/2004 = 302


I also need to take into consideration the leap years accordingly

TIA

Hi

=DATE(C1,B1,A1)
=DATEDIF(DATE(YEAR(A1),1,0),A1,"D")


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)



"Anthony Slater" <Anthony wrote in message
...
Q1
I have inherited a spreadsheet that has the date split over 3 columns: -
a1 = day
b1 = month
c1 = year

How can I put these in one column to read dd/mm/yyy

Q2

How can I work out the day number of a particular date?

Ie 28/10/2004 = 302


I also need to take into consideration the leap years accordingly

TIA

Q1:

=DATE(C1,B1,A1)

Q2:

="28/10/04"-"1/1/04"+1

HTH
Jason
Atlanta, GA

-----Original Message-----
Q1
I have inherited a spreadsheet that has the date split
over 3 columns: -
a1 = day
b1 = month
c1 = year

How can I put these in one column to read dd/mm/yyy

Q2

How can I work out the day number of a particular date?

Ie 28/10/2004 = 302


I also need to take into consideration the leap years
accordingly

TIA

.

Hi
Q1
In D1 enter
=DATE(C1,B1,A1)
and format as date

Q2:
=DAY(A1)-DAY(DATE(2003,12,31))

"Anthony Slater" wrote:

Q1
I have inherited a spreadsheet that has the date split over 3 columns: -
a1 = day
b1 = month
c1 = year

How can I put these in one column to read dd/mm/yyy

Q2

How can I work out the day number of a particular date?

Ie 28/10/2004 = 302


I also need to take into consideration the leap years accordingly

TIA

A1:
One way:

=DATE(C1,B1,A1)


A2:
One way:

If your date is in A1:

=A1-DATE(YEAR(A1),1,0)

Format the cell as General or another Number format.



In article ,
"Anthony Slater" <Anthony wrote:

Q1
I have inherited a spreadsheet that has the date split over 3 columns: -
a1 = day
b1 = month
c1 = year

How can I put these in one column to read dd/mm/yyy

Q2

How can I work out the day number of a particular date?

Ie 28/10/2004 = 302


I also need to take into consideration the leap years accordingly

Also, if the month specified in B1 is the actual name of the month
(January, February, etc.) instead of the number corresponding to the
month...

=DATEVALUE(B1&" "&A1&", "&C1)

Hope this helps!

In article ,
"Anthony Slater" <Anthony wrote:

Q1
I have inherited a spreadsheet that has the date split over 3 columns: -
a1 = day
b1 = month
c1 = year

How can I put these in one column to read dd/mm/yyy

Q2

How can I work out the day number of a particular date?

Ie 28/10/2004 = 302


I also need to take into consideration the leap years accordingly

TIA

Thanks to all for your replies

They all worked in their own different ways !!

Have a good Christmas



"Jason Morin" wrote:

Q1:

=DATE(C1,B1,A1)

Q2:

="28/10/04"-"1/1/04"+1

HTH
Jason
Atlanta, GA

-----Original Message-----
Q1
I have inherited a spreadsheet that has the date split
over 3 columns: -
a1 = day
b1 = month
c1 = year

How can I put these in one column to read dd/mm/yyy

Q2

How can I work out the day number of a particular date?

Ie 28/10/2004 = 302


I also need to take into consideration the leap years
accordingly

TIA

.