Return Year from =Now()

[RickM]

I am trying to return in another cell the year only. I am using that in another formula. What would the formula be for:
A1 =Now()
I want it to be 2011 in B1

 

[shg]

=year(a1) and format as Number

 

[RickM]

Thanks. I thought that was what I wanted but I really need cell B1 to be 1/1/2011 from =Now(). Therefore it will change to 1/1/2012 next year, etc..... IS there a better way to do this?

 

[Aladin Akyurek]

Thanks. I thought that was what I wanted but I really need cell B1 to be 1/1/2011 from =Now(). Therefore it will change to 1/1/2012 next year, etc..... IS there a better way to do this?

Something like...

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

 

[T. Valko]

Thanks. I thought that was what I wanted but I really need cell B1 to be 1/1/2011 from =Now(). Therefore it will change to 1/1/2012 next year, etc..... IS there a better way to do this?

Try this...

If cell A1 contains the formula: =NOW()...

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

Or, just put it directly in one formula:

=DATE(YEAR(NOW()),1,1)

 

[RickM]

If I put it directly into the formula
=SUMIFS(E$3:E$30000,$D$3:$D$30000,$Z3,$B$3:$B$30000,">="&$Z$2)
where Z2 is the date.
What would I replace ">="&$Z$2 with.

 

[T. Valko]

If I put it directly into the formula
=SUMIFS(E$3:E$30000,$D$3:$D$30000,$Z3,$B$3:$B$30000,">="&$Z$2)
where Z2 is the date.
What would I replace ">="&$Z$2 with.

Well, if you want to use the reference in that formula then I would just put the date formula in another cell and then refer to that cell as it looks like you're doing now. I like to put as many of the criteria in cells as I can.

Here it is all in one formula:

=SUMIFS(E$3:E$30000,$D$3:$D$30000,$Z3,$B$3:$B$30000,">="&DATE(YEAR(NOW()),1,1))

 

[RickM]

PERFECT...... Many Thanks

 

[T. Valko]

PERFECT...... Many Thanks

You're welcome. Thanks for the feedback!