How to determine the date?
 

Does anyone have any suggestions on how to determine the date?

If cell A1 is week, then I would like to show next coming Sunday in cell B1,
If today is Sunday, then show today in cell B1.

[Repeat requirements for Month]
If cell A1 is month, then I would like to show the last day of this month in
cell B1,
If today is the last day of this month, then show today in cell B1.

[Repeat requirements for Quarter]
If cell A1 is Quarter, then I would like to show the last day of this
quarter in cell B1,
If today is the last day of this quarter, then show today in cell B1.

[Repeat requirements for Year]
If cell A1 is Year, then I would like to show the last day of this year in
cell B1,
If today is the last day of this year, then show today in cell B1.

Does anyone have any suggestions on how to do it?
Thank you in advance for any suggestions
Eric

How to determine the date?
 

ENTER THE FOLLOWING FORMULA IN B1 CELL TO SOLVE YOUR SUNDAY PROBLEM

=IF(WEEKDAY(A1)=1,A1,A1+(8-WEEKDAY(A1)))

"Eric" wrote:

Does anyone have any suggestions on how to determine the date?

If cell A1 is week, then I would like to show next coming Sunday in cell B1,
If today is Sunday, then show today in cell B1.

[Repeat requirements for Month]
If cell A1 is month, then I would like to show the last day of this month in
cell B1,
If today is the last day of this month, then show today in cell B1.

[Repeat requirements for Quarter]
If cell A1 is Quarter, then I would like to show the last day of this
quarter in cell B1,
If today is the last day of this quarter, then show today in cell B1.

[Repeat requirements for Year]
If cell A1 is Year, then I would like to show the last day of this year in
cell B1,
If today is the last day of this year, then show today in cell B1.

Does anyone have any suggestions on how to do it?
Thank you in advance for any suggestions
Eric

How to determine the date?
 

On Wed, 16 May 2007 02:21:00 -0700, Eric
wrote:

Does anyone have any suggestions on how to determine the date?

If cell A1 is week, then I would like to show next coming Sunday in cell B1,
If today is Sunday, then show today in cell B1.

[Repeat requirements for Month]
If cell A1 is month, then I would like to show the last day of this month in
cell B1,
If today is the last day of this month, then show today in cell B1.

[Repeat requirements for Quarter]
If cell A1 is Quarter, then I would like to show the last day of this
quarter in cell B1,
If today is the last day of this quarter, then show today in cell B1.

[Repeat requirements for Year]
If cell A1 is Year, then I would like to show the last day of this year in
cell B1,
If today is the last day of this year, then show today in cell B1.

Does anyone have any suggestions on how to do it?
Thank you in advance for any suggestions
Eric

Here are some formulas. You should be able to work out the IF configuration,
and how to set it up using data validation:

week =TODAY()+7-WEEKDAY(TODAY()-1)
month =DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
Quarter =DATE(YEAR(TODAY()),(INT((MONTH(TODAY())-1)/3)+1)*3+1,0)
Year =DATE(YEAR(TODAY()),12,31)


--ron