| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with DateValue function.
I have the following formula in a cell:-
=IF($A$4="spring",DATEVALUE("31-12-06),FALSE), which works fine. However to make the formula more flexible, I dont want to hard code the year. Basically the year part should be current year minus 1 (i.e. 2007-1). Please note that the date in the DateValue field will always be 31st December current year -1 Can someone advise me how I can do that? Thank you. |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Help with DateValue function.
Hi Pank,
Try: =IF($A$4="spring",DATE(YEAR(TODAY()-1),12,31),FALSE) Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Pank" wrote in message ... I have the following formula in a cell:- =IF($A$4="spring",DATEVALUE("31-12-06),FALSE), which works fine. However to make the formula more flexible, I dont want to hard code the year. Basically the year part should be current year minus 1 (i.e. 2007-1). Please note that the date in the DateValue field will always be 31st December current year -1 Can someone advise me how I can do that? Thank you. |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Help with DateValue function.
Hi macropod,
Firstly, many thanks for the promt response. Having put the formula in it gives a date of 31/12/2007 and not 31/12/2006 as I want. ANy ideas what needs to be changed. Pank "macropod" wrote: Hi Pank, Try: =IF($A$4="spring",DATE(YEAR(TODAY()-1),12,31),FALSE) Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Pank" wrote in message ... I have the following formula in a cell:- =IF($A$4="spring",DATEVALUE("31-12-06),FALSE), which works fine. However to make the formula more flexible, I dont want to hard code the year. Basically the year part should be current year minus 1 (i.e. 2007-1). Please note that the date in the DateValue field will always be 31st December current year -1 Can someone advise me how I can do that? Thank you. |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Help with DateValue function.
Parens in the wrong place. Could use
=IF($A$4="spring",DATE(YEAR(TODAY())-1,12,31),FALSE) or use the formula in my other reply. In article , "macropod" wrote: Hi Pank, Try: =IF($A$4="spring",DATE(YEAR(TODAY()-1),12,31),FALSE) Cheers |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Help with DateValue function.
One way:
=IF($A$4="spring",DATE(YEAR(TODAY()),1,0),FALSE) Note that the 0th day of the month, to XL, is the last day of the previous month. In article , Pank wrote: I have the following formula in a cell:- =IF($A$4="spring",DATEVALUE("31-12-06),FALSE), which works fine. However to make the formula more flexible, I dont want to hard code the year. Basically the year part should be current year minus 1 (i.e. 2007-1). Please note that the date in the DateValue field will always be 31st December current year -1 Can someone advise me how I can do that? Thank you. |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Help with DateValue function.
Hi JE McGimpsey,
Thank you for you help, you formula, works as expected retuning a date of 31/12/06. Unfortunately, I now have to cater for other date and not just 31/12/06. Therefore macropod formula would be perfect but for the year being returned as the current year and not the previous year. Any ideas how to change macropod formula so that it returns the previous year? "JE McGimpsey" wrote: One way: =IF($A$4="spring",DATE(YEAR(TODAY()),1,0),FALSE) Note that the 0th day of the month, to XL, is the last day of the previous month. In article , Pank wrote: I have the following formula in a cell:- =IF($A$4="spring",DATEVALUE("31-12-06â),FALSE), which works fine. However to make the formula more flexible, I donât want to hard code the year. Basically the year part should be current year minus 1 (i.e. 2007-1). Please note that the date in the DateValue field will always be 31st December current year -1 Can someone advise me how I can do that? Thank you. |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Help with DateValue function.
Sorry,
The '-1' was in the wrong place. As JE noted, the formula should have been: =IF($A$4="spring",DATE(YEAR(TODAY())-1,12,31),FALSE) Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Pank" wrote in message ... Hi JE McGimpsey, Thank you for you help, you formula, works as expected retuning a date of 31/12/06. Unfortunately, I now have to cater for other date and not just 31/12/06. Therefore macropod formula would be perfect but for the year being returned as the current year and not the previous year. Any ideas how to change macropod formula so that it returns the previous year? "JE McGimpsey" wrote: One way: =IF($A$4="spring",DATE(YEAR(TODAY()),1,0),FALSE) Note that the 0th day of the month, to XL, is the last day of the previous month. In article , Pank wrote: I have the following formula in a cell:- =IF($A$4="spring",DATEVALUE("31-12-06â),FALSE), which works fine. However to make the formula more flexible, I donât want to hard code the year. Basically the year part should be current year minus 1 (i.e. 2007-1). Please note that the date in the DateValue field will always be 31st December current year -1 Can someone advise me how I can do that? Thank you. |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Help with DateValue function.
Macropod and JE McGimpsey,
Worked a treat, many thanks for your assistance, much appreciated. "macropod" wrote: Sorry, The '-1' was in the wrong place. As JE noted, the formula should have been: =IF($A$4="spring",DATE(YEAR(TODAY())-1,12,31),FALSE) Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Pank" wrote in message ... Hi JE McGimpsey, Thank you for you help, you formula, works as expected retuning a date of 31/12/06. Unfortunately, I now have to cater for other date and not just 31/12/06. Therefore macropod formula would be perfect but for the year being returned as the current year and not the previous year. Any ideas how to change macropod formula so that it returns the previous year? "JE McGimpsey" wrote: One way: =IF($A$4="spring",DATE(YEAR(TODAY()),1,0),FALSE) Note that the 0th day of the month, to XL, is the last day of the previous month. In article , Pank wrote: I have the following formula in a cell:- =IF($A$4="spring",DATEVALUE("31-12-06â),FALSE), which works fine. However to make the formula more flexible, I donât want to hard code the year. Basically the year part should be current year minus 1 (i.e. 2007-1). Please note that the date in the DateValue field will always be 31st December current year -1 Can someone advise me how I can do that? Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help With DATEVALUE | Excel Worksheet Functions | |||
| datevalue() | Excel Worksheet Functions | |||
| datevalue | Excel Worksheet Functions | |||
| Conditional Sum and DATEVALUE function | Excel Worksheet Functions | |||
| datevalue | Excel Worksheet Functions | |||
Hybrid Mode
