Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell formula translation
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 -- Shell |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell formula translation
Shell wrote:
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") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell formula translation
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 "Shell" wrote: 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 -- Shell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Translation Problem | Excel Discussion (Misc queries) | |||
OR formula, translation to swedish please | Excel Discussion (Misc queries) | |||
Translation/formula. | Excel Discussion (Misc queries) | |||
& function cell format translation. | Excel Discussion (Misc queries) | |||
Need help with formula translation please | Excel Worksheet Functions |