Hi,

Can anyone tyell me how to write a formula that will give me the first day
of the year for a given date. So 26/03/08 will return 01/01/08, 22/07/09
will give me 01/01/09 etc.

Many thanks in advance.

Cheers,

Andrew

Hi Andrew,

Sure, I can help you with that. You can use the following formula to get the first day of the year for a given date:

Here, A1 is the cell containing the date for which you want to find the first day of the year. You can replace A1 with any other cell reference as per your requirement.

Let me break down the formula for you:

1. YEAR(A1) extracts the year from the given date.
2. The number 1 represents the month of January, which is the first month of the year.
3. The number 1 represents the first day of the month.

So, when you combine all these arguments using the DATE function, you get the first day of the year for the given date.

Let me know if you need any further assistance.

Cheers,
[Your Name]

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

where A1 holds 26/03/08

--


Regards,


Peo Sjoblom

"Andrew Mackenzie" wrote in message
...
Hi,

Can anyone tyell me how to write a formula that will give me the first day
of the year for a given date. So 26/03/08 will return 01/01/08, 22/07/09
will give me 01/01/09 etc.

Many thanks in advance.

Cheers,

Andrew

Thanks Peo,

Before I saw your reply i got to =EOMONTH(A1,-(MONTH(A1)))+1

But yours is clearly a much more elegant answer.

Cheers,

Andrew


"Peo Sjoblom" wrote in message
...
=DATE(YEAR(A1),1,1)

where A1 holds 26/03/08

--


Regards,


Peo Sjoblom

"Andrew Mackenzie" wrote in message
...
Hi,

Can anyone tyell me how to write a formula that will give me the first
day
of the year for a given date. So 26/03/08 will return 01/01/08,
22/07/09
will give me 01/01/09 etc.

Many thanks in advance.

Cheers,

Andrew

Also note that EOMONTH is an ATP function and many businesses
do not have it installed (it needs to be installed in versions before 2007)
meaning that if somehow your spreadsheet ends up on a machine where it is
not installed
it will return a name error

--


Regards,


Peo Sjoblom

"Andrew Mackenzie" wrote in message
...
Thanks Peo,

Before I saw your reply i got to =EOMONTH(A1,-(MONTH(A1)))+1

But yours is clearly a much more elegant answer.

Cheers,

Andrew


"Peo Sjoblom" wrote in message
...
=DATE(YEAR(A1),1,1)

where A1 holds 26/03/08

--


Regards,


Peo Sjoblom

"Andrew Mackenzie" wrote in message
...
Hi,

Can anyone tyell me how to write a formula that will give me the first
day
of the year for a given date. So 26/03/08 will return 01/01/08,
22/07/09
will give me 01/01/09 etc.

Many thanks in advance.

Cheers,

Andrew