| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula required
I need a formula that will enter a date in a cell based on today's date. I'll
explain. If today's date is from the 1st of the month to the 15th of the month, I need the 25th day of the previous month in the cell. If the date is from the 16th of the month to the last day of the month, I need the 25th day of the present month to appear in the cell. Examples Today's date Cell date 11/16/2006 11/25/2006 12/5/2006 11/25/2006 1/13/2007 12/25/2006 I hope I have explained clearly. TIA David |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula required
=IF(A1-DATE(YEAR(A1),MONTH(A1),0)<=15,DATE(YEAR(A1),MONTH (A1)-1,25),DATE(YEAR(A1),MONTH(A1),25))
for a value in cell A1. -- Allllen "Compass Rose" wrote: I need a formula that will enter a date in a cell based on today's date. I'll explain. If today's date is from the 1st of the month to the 15th of the month, I need the 25th day of the previous month in the cell. If the date is from the 16th of the month to the last day of the month, I need the 25th day of the present month to appear in the cell. Examples Today's date Cell date 11/16/2006 11/25/2006 12/5/2006 11/25/2006 1/13/2007 12/25/2006 I hope I have explained clearly. TIA David |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula required
....don't forget to format it as a date (format cell number date)
-- Allllen "Allllen" wrote: =IF(A1-DATE(YEAR(A1),MONTH(A1),0)<=15,DATE(YEAR(A1),MONTH (A1)-1,25),DATE(YEAR(A1),MONTH(A1),25)) for a value in cell A1. -- Allllen "Compass Rose" wrote: I need a formula that will enter a date in a cell based on today's date. I'll explain. If today's date is from the 1st of the month to the 15th of the month, I need the 25th day of the previous month in the cell. If the date is from the 16th of the month to the last day of the month, I need the 25th day of the present month to appear in the cell. Examples Today's date Cell date 11/16/2006 11/25/2006 12/5/2006 11/25/2006 1/13/2007 12/25/2006 I hope I have explained clearly. TIA David |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula required
Or, slightly shorter,
=IF(DAY(A1)<=15,DATE(YEAR(A1),MONTH(A1)-1,25),DATE(YEAR(A1),MONTH(A1),25)) -- David Biddulph "Allllen" wrote in message ... =IF(A1-DATE(YEAR(A1),MONTH(A1),0)<=15,DATE(YEAR(A1),MONTH (A1)-1,25),DATE(YEAR(A1),MONTH(A1),25)) for a value in cell A1. -- Allllen "Compass Rose" wrote: I need a formula that will enter a date in a cell based on today's date. I'll explain. If today's date is from the 1st of the month to the 15th of the month, I need the 25th day of the previous month in the cell. If the date is from the 16th of the month to the last day of the month, I need the 25th day of the present month to appear in the cell. Examples Today's date Cell date 11/16/2006 11/25/2006 12/5/2006 11/25/2006 1/13/2007 12/25/2006 I hope I have explained clearly. TIA David |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula required
Or even shorter:
=DATE(YEAR(A1),MONTH(A1)-(DAY(A1)<=15),25) In article , "David Biddulph" wrote: Or, slightly shorter, =IF(DAY(A1)<=15,DATE(YEAR(A1),MONTH(A1)-1,25),DATE(YEAR(A1),MONTH(A1),25)) -- David Biddulph "Allllen" wrote in message ... =IF(A1-DATE(YEAR(A1),MONTH(A1),0)<=15,DATE(YEAR(A1),MONTH (A1)-1,25),DATE(YEA R(A1),MONTH(A1),25)) for a value in cell A1. -- Allllen "Compass Rose" wrote: I need a formula that will enter a date in a cell based on today's date. I'll explain. If today's date is from the 1st of the month to the 15th of the month, I need the 25th day of the previous month in the cell. If the date is from the 16th of the month to the last day of the month, I need the 25th day of the present month to appear in the cell. Examples Today's date Cell date 11/16/2006 11/25/2006 12/5/2006 11/25/2006 1/13/2007 12/25/2006 I hope I have explained clearly. TIA David |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula required
Thanks Allllen, but your formula is based on the assumption that today's date
is in A1, which isn't the case. Today's date doesn't appear in any of the cells of the worksheet, so I would like the formula to reference TODAY() to arrive at the correct date, not reference a cell. I could put today's date in a cell and then hide it, but I would like to avoid that if I can. David "Allllen" wrote: =IF(A1-DATE(YEAR(A1),MONTH(A1),0)<=15,DATE(YEAR(A1),MONTH (A1)-1,25),DATE(YEAR(A1),MONTH(A1),25)) for a value in cell A1. -- "Compass Rose" wrote: I need a formula that will enter a date in a cell based on today's date. I'll explain. If today's date is from the 1st of the month to the 15th of the month, I need the 25th day of the previous month in the cell. If the date is from the 16th of the month to the last day of the month, I need the 25th day of the present month to appear in the cell. Examples Today's date Cell date 11/16/2006 11/25/2006 12/5/2006 11/25/2006 1/13/2007 12/25/2006 I hope I have explained clearly. TIA David |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula required
Figured it out, I think:
=DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY())<=15),25) David "Allllen" wrote: =IF(A1-DATE(YEAR(A1),MONTH(A1),0)<=15,DATE(YEAR(A1),MONTH (A1)-1,25),DATE(YEAR(A1),MONTH(A1),25)) for a value in cell A1. -- Allllen "Compass Rose" wrote: I need a formula that will enter a date in a cell based on today's date. I'll explain. If today's date is from the 1st of the month to the 15th of the month, I need the 25th day of the previous month in the cell. If the date is from the 16th of the month to the last day of the month, I need the 25th day of the present month to appear in the cell. Examples Today's date Cell date 11/16/2006 11/25/2006 12/5/2006 11/25/2006 1/13/2007 12/25/2006 I hope I have explained clearly. TIA David |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula required
One way:
=DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY())<=15),25) In article , Compass Rose wrote: Thanks Allllen, but your formula is based on the assumption that today's date is in A1, which isn't the case. Today's date doesn't appear in any of the cells of the worksheet, so I would like the formula to reference TODAY() to arrive at the correct date, not reference a cell. I could put today's date in a cell and then hide it, but I would like to avoid that if I can. David "Allllen" wrote: =IF(A1-DATE(YEAR(A1),MONTH(A1),0)<=15,DATE(YEAR(A1),MONTH (A1)-1,25),DATE(YEA R(A1),MONTH(A1),25)) for a value in cell A1. -- "Compass Rose" wrote: I need a formula that will enter a date in a cell based on today's date. I'll explain. If today's date is from the 1st of the month to the 15th of the month, I need the 25th day of the previous month in the cell. If the date is from the 16th of the month to the last day of the month, I need the 25th day of the present month to appear in the cell. Examples Today's date Cell date 11/16/2006 11/25/2006 12/5/2006 11/25/2006 1/13/2007 12/25/2006 I hope I have explained clearly. TIA David |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula required
Thanks for the help, JE.
I hate to get picky, but is there a way to format the cell so that the month appears in upper case, rather than lower? David "JE McGimpsey" wrote: One way: =DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY())<=15),25) In article , Compass Rose wrote: Thanks Allllen, but your formula is based on the assumption that today's date is in A1, which isn't the case. Today's date doesn't appear in any of the cells of the worksheet, so I would like the formula to reference TODAY() to arrive at the correct date, not reference a cell. I could put today's date in a cell and then hide it, but I would like to avoid that if I can. David "Allllen" wrote: =IF(A1-DATE(YEAR(A1),MONTH(A1),0)<=15,DATE(YEAR(A1),MONTH (A1)-1,25),DATE(YEA R(A1),MONTH(A1),25)) for a value in cell A1. -- "Compass Rose" wrote: I need a formula that will enter a date in a cell based on today's date. I'll explain. If today's date is from the 1st of the month to the 15th of the month, I need the 25th day of the previous month in the cell. If the date is from the 16th of the month to the last day of the month, I need the 25th day of the present month to appear in the cell. Examples Today's date Cell date 11/16/2006 11/25/2006 12/5/2006 11/25/2006 1/13/2007 12/25/2006 I hope I have explained clearly. TIA David |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula required
Format, no (other than using a font with only upper case characters),
but you could us =UPPER(DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY())<=15),25)) In article , Compass Rose wrote: Thanks for the help, JE. I hate to get picky, but is there a way to format the cell so that the month appears in upper case, rather than lower? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula required
Thanks JE. I tried your suggestion but it returned the serial value of the
date. I tweeked to =UPPER(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY())<=15),25),"MMMM d, yyyy") which did the trick. David "JE McGimpsey" wrote: Format, no (other than using a font with only upper case characters), but you could us =UPPER(DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY())<=15),25)) In article , Compass Rose wrote: Thanks for the help, JE. I hate to get picky, but is there a way to format the cell so that the month appears in upper case, rather than lower? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Trending Formula Results by Date | Excel Worksheet Functions | |||
| Year-to-date year to date formula | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
