Cell formula translation

S

Shell

I have a cell C1 with a formula =Now() This gives me 2010
I have another cell C2 with a formula =Now()-100 This gives m1 2009

So far so good

I need to to create a cell value as follows 12/22/LY - 1/21/CY
Where LY would refer back to cell C2 and CY would refer back to cell C1.
This cell would then become 12/22/2009 - 1/21/2010. I cannot use Excel
macros. I would be wrapping this in an if statement so that the next cell
would be 1/22/CY - 2/21/CY

Any help would be appreciated. Thanks
 
G

Glenn

Shell said:
I have a cell C1 with a formula =Now() This gives me 2010
I have another cell C2 with a formula =Now()-100 This gives m1 2009

So far so good

I need to to create a cell value as follows 12/22/LY - 1/21/CY
Where LY would refer back to cell C2 and CY would refer back to cell C1.
This cell would then become 12/22/2009 - 1/21/2010. I cannot use Excel
macros. I would be wrapping this in an if statement so that the next cell
would be 1/22/CY - 2/21/CY

Any help would be appreciated. Thanks

=NOW() does not give you 2010. It gives you the current date/time, which you
may have formatted to show just the year. If you want the current year, use this:

=YEAR(TODAY())

And last year would be this:

=YEAR(TODAY())-1


As to your desired results, there may be numerous ways to accomplish it. Not
sure why you are "wrapping this in an if statement". Maybe try something like this:

=TEXT(DATE(YEAR(TODAY()),ROW(1:1)-1,22),"M/D/YYYY")&" - "&
TEXT(DATE(YEAR(TODAY()),ROW(1:1),21),"M/D/YYYY")
 
S

Sheeloo

Are you looking for
="12/22/"&TEXT(C2,"yyyy")& " - 1/21/" & TEXT(C1,"yyyy")

this will give you the string 12/22/2009 - 1/21/2010
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top