Excel Date Formula Question
 

Today is 07/29/2006, and I've entered that into cell A1 using the
formula =now()
In cell A2, I want to return the first day of the current year (i.e.
01/01/2006).
Is there an Excel formula you can create to do this, so that annual
spreadsheets can automatically updated using the =now() function as a
base date?
Thanks.

Excel Date Formula Question
 

You can use this in A2

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Cactus1993" wrote in message oups.com...
Today is 07/29/2006, and I've entered that into cell A1 using the
formula =now()
In cell A2, I want to return the first day of the current year (i.e.
01/01/2006).
Is there an Excel formula you can create to do this, so that annual
spreadsheets can automatically updated using the =now() function as a
base date?
Thanks.

Excel Date Formula Question
 

Try this:

=DATE(YEAR(TODAY()),1,1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Cactus1993" wrote:

Today is 07/29/2006, and I've entered that into cell A1 using the
formula =now()
In cell A2, I want to return the first day of the current year (i.e.
01/01/2006).
Is there an Excel formula you can create to do this, so that annual
spreadsheets can automatically updated using the =now() function as a
base date?
Thanks.

Excel Date Formula Question
 

To both Rons:

Thanks, guys. Both formulas work, and I can see using each in different
instances in my Excel program. Probably simple enough for you guys ...
but I was wrestling with it because I'm not well versed in date formula
calculations.

Thanks again, and for following up so fast!!!


Ron Coderre wrote:
Try this:

=DATE(YEAR(TODAY()),1,1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Cactus1993" wrote:

Today is 07/29/2006, and I've entered that into cell A1 using the
formula =now()
In cell A2, I want to return the first day of the current year (i.e.
01/01/2006).
Is there an Excel formula you can create to do this, so that annual
spreadsheets can automatically updated using the =now() function as a
base date?
Thanks.

Excel Date Formula Question
 

Cactus1993:

Try this

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

Matt


--
Flintstone
------------------------------------------------------------------------
Flintstone's Profile: http://www.excelforum.com/member.php...o&userid=15310
View this thread: http://www.excelforum.com/showthread...hreadid=566337

Excel Date Formula Question
 

Although this formula works, it may not be the most efficient:

=A1-(A1-DATE(YEAR(A1),1,1))
is the same as:
=A1-A1+DATE(YEAR(A1),1,1))
or
=DATE(YEAR(A1),1,1)



Flintstone wrote:

Cactus1993:

Try this

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

Matt

--
Flintstone
------------------------------------------------------------------------
Flintstone's Profile: http://www.excelforum.com/member.php...o&userid=15310
View this thread: http://www.excelforum.com/showthread...hreadid=566337

--

Dave Peterson

Excel Date Formula Question
 

Thanks again ... I appreciate the help.


Dave Peterson wrote:
Although this formula works, it may not be the most efficient:

=A1-(A1-DATE(YEAR(A1),1,1))
is the same as:
=A1-A1+DATE(YEAR(A1),1,1))
or
=DATE(YEAR(A1),1,1)



Flintstone wrote:

Cactus1993:

Try this

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

Matt

--
Flintstone
------------------------------------------------------------------------
Flintstone's Profile: http://www.excelforum.com/member.php...o&userid=15310
View this thread: http://www.excelforum.com/showthread...hreadid=566337

--

Dave Peterson

Excel Date Formula Question
 

If we take efficiency to the extremes; any formula solution will recalculate
on every new cell entry in the workbook, while this job has to be done only
once a year. I'd put a small macro solution in the workbook_open event to
check which year it is and hardcode that proper new year date into the cell,
unless it's a very lightweight workbook that is.

Best wishes Harald

"Dave Peterson" skrev i melding
...
Although this formula works, it may not be the most efficient:

Excel Date Formula Question
 

Or even just type the date in on January 1st each year.

Harald Staff wrote:

If we take efficiency to the extremes; any formula solution will recalculate
on every new cell entry in the workbook, while this job has to be done only
once a year. I'd put a small macro solution in the workbook_open event to
check which year it is and hardcode that proper new year date into the cell,
unless it's a very lightweight workbook that is.

Best wishes Harald

"Dave Peterson" skrev i melding
...
Although this formula works, it may not be the most efficient:

--

Dave Peterson

Excel Date Formula Question
 

I wanted to fully automate a financial analysis workbook with several
tabs to calculate YTD returns, YTD % returns, and other YTD
calculations -- using the difference between the current date and the
first day of that current year, as a denominator. Full automation is
the reason I needed this formula. (Didn't want you to think I was just
being lazy. :-))


Dave Peterson wrote:
Or even just type the date in on January 1st each year.

Harald Staff wrote:

If we take efficiency to the extremes; any formula solution will recalculate
on every new cell entry in the workbook, while this job has to be done only
once a year. I'd put a small macro solution in the workbook_open event to
check which year it is and hardcode that proper new year date into the cell,
unless it's a very lightweight workbook that is.

Best wishes Harald

"Dave Peterson" skrev i melding
...
Although this formula works, it may not be the most efficient:

--

Dave Peterson