Excel Date Formula Question

  • Thread starter Thread starter Cactus1993
  • Start date Start date
C

Cactus1993

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.
 
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!!!
 
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)
 
Thanks again ... I appreciate the help.


Dave said:
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)
Click to expand...
 
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
 
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. :-))
 
Back
Top