| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Date formula
Good Morning Excel Masters,
I would like to ask for help. I have a report where I have to show only Monday, Wednesday and Friday dates. I need to go backwards starting with todays date. So for example in A2 I will put =today(). However in A3 I need the formula to show the date for the previous Wednesday (3/22). Now writing this is generally easy, however when Monday (3/27) comes around I will need A3 to show the date for Friday (3/24), and then A4 will need to show Wednesdays date (3/22) etc€¦ Your help is appreciated. JR |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Date formula
Put this formula in A3 and copy down the column.
=IF(WEEKDAY(A2)=2,A2-3,A2-2) -- Carlos "JR" wrote in message ... Good Morning Excel Masters, I would like to ask for help. I have a report where I have to show only Monday, Wednesday and Friday dates. I need to go backwards starting with today's date. So for example in A2 I will put =today(). However in A3 I need the formula to show the date for the previous Wednesday (3/22). Now writing this is generally easy, however when Monday (3/27) comes around I will need A3 to show the date for Friday (3/24), and then A4 will need to show Wednesday's date (3/22) etc. Your help is appreciated. JR |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Date formula
perfect
"CarlosAntenna" wrote: Put this formula in A3 and copy down the column. =IF(WEEKDAY(A2)=2,A2-3,A2-2) -- Carlos "JR" wrote in message ... Good Morning Excel Masters, I would like to ask for help. I have a report where I have to show only Monday, Wednesday and Friday dates. I need to go backwards starting with today's date. So for example in A2 I will put =today(). However in A3 I need the formula to show the date for the previous Wednesday (3/22). Now writing this is generally easy, however when Monday (3/27) comes around I will need A3 to show the date for Friday (3/24), and then A4 will need to show Wednesday's date (3/22) etc. Your help is appreciated. JR |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Date formula
Of course that only works if the date in A2 is a M, W, or F.
If A2 can be any date, you would need something like this: =IF(WEEKDAY(A2)=2,A2-3,IF(WEEKDAY(A2)=1,A2-2,IF(MOD(WEEKDAY(A2),2)<0,A2-1,A 2-2))) You may need to consider this because the today() function is volatile. -- Carlos "JR" wrote in message ... perfect "CarlosAntenna" wrote: Put this formula in A3 and copy down the column. =IF(WEEKDAY(A2)=2,A2-3,A2-2) -- Carlos "JR" wrote in message ... Good Morning Excel Masters, I would like to ask for help. I have a report where I have to show only Monday, Wednesday and Friday dates. I need to go backwards starting with today's date. So for example in A2 I will put =today(). However in A3 I need the formula to show the date for the previous Wednesday (3/22). Now writing this is generally easy, however when Monday (3/27) comes around I will need A3 to show the date for Friday (3/24), and then A4 will need to show Wednesday's date (3/22) etc. Your help is appreciated. JR |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Date formula
With a valid date in A2, another option for A3 might be:
=A2-MOD(128,WEEKDAY(A2)+3) and copy down. -- HTH. :) Dana DeLouis Windows XP, Office 2003 "JR" wrote in message ... Good Morning Excel Masters, I would like to ask for help. I have a report where I have to show only Monday, Wednesday and Friday dates. I need to go backwards starting with today's date. So for example in A2 I will put =today(). However in A3 I need the formula to show the date for the previous Wednesday (3/22). Now writing this is generally easy, however when Monday (3/27) comes around I will need A3 to show the date for Friday (3/24), and then A4 will need to show Wednesday's date (3/22) etc. Your help is appreciated. JR |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Date formula
If A2 can be any date, ...
=IF(WEEKDAY(A2)=2,A2-3,IF(WEEKDAY(A2)=1,A2-2,IF(MOD(WEEKDAY(A2),2)<0,A2-1,A 2-2))) Just another option with any date in A1, then copied down. =A1-MOD(86349937, 6*WEEKDAY(A1) - 1) -- HTH. :) Dana DeLouis Windows XP, Office 2003 "CarlosAntenna" wrote in message ... Of course that only works if the date in A2 is a M, W, or F. If A2 can be any date, you would need something like this: =IF(WEEKDAY(A2)=2,A2-3,IF(WEEKDAY(A2)=1,A2-2,IF(MOD(WEEKDAY(A2),2)<0,A2-1,A 2-2))) You may need to consider this because the today() function is volatile. -- Carlos "JR" wrote in message ... perfect "CarlosAntenna" wrote: Put this formula in A3 and copy down the column. =IF(WEEKDAY(A2)=2,A2-3,A2-2) -- Carlos "JR" wrote in message ... Good Morning Excel Masters, I would like to ask for help. I have a report where I have to show only Monday, Wednesday and Friday dates. I need to go backwards starting with today's date. So for example in A2 I will put =today(). However in A3 I need the formula to show the date for the previous Wednesday (3/22). Now writing this is generally easy, however when Monday (3/27) comes around I will need A3 to show the date for Friday (3/24), and then A4 will need to show Wednesday's date (3/22) etc. Your help is appreciated. JR |
| 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 | |||
| Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
Linear Mode
