Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determine day of the week from date | Excel Discussion (Misc queries) | |||
Determine which column to use by date | Excel Worksheet Functions | |||
Function determine if date is even or odd | Excel Worksheet Functions | |||
How can I determine the century of a date in Excel? | Excel Worksheet Functions | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |