Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having a cell equal a specific day of the week?
Right now in some of my spreadsheets, I have cells equal to things such as =Today()-3 to go 3 days previous. In writing that I am actually looking to find the previous Friday, since I will be running this on Mondays. Is there a way to specifically call on "the previous Friday" so that if I happen to run it on Tuesday it will still pickup last Friday? I'm sure its a line of code, just not sure where to look for it. Thanks, ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569940 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having a cell equal a specific day of the week?
=TODAY()-WEEKDAY(TODAY()-6)
-- Kind regards, Niek Otten Microsoft MVP - Excel "nbaj2k" wrote in message ... | | Right now in some of my spreadsheets, I have cells equal to things such | as =Today()-3 to go 3 days previous. In writing that I am actually | looking to find the previous Friday, since I will be running this on | Mondays. Is there a way to specifically call on "the previous Friday" | so that if I happen to run it on Tuesday it will still pickup last | Friday? | | I'm sure its a line of code, just not sure where to look for it. | | Thanks, | | ~J | | | -- | nbaj2k | ------------------------------------------------------------------------ | nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 | View this thread: http://www.excelforum.com/showthread...hreadid=569940 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having a cell equal a specific day of the week?
Not sure if this what you want, but try this code
Sub test() Range("A1").Select ' Have your date in Cell A1 ' The loop will lookup the previous Friday ' and eneter that date two cells down For c = Selection.Value To (Selection.Value - 7) Step -1 backdate = Format(c, "dddd dd mmm yyyy") If Left(backdate, 3) = "Fri" Then Selection.Offset(2, 0) = backdate End If Next End Sub Chas "nbaj2k" wrote: Right now in some of my spreadsheets, I have cells equal to things such as =Today()-3 to go 3 days previous. In writing that I am actually looking to find the previous Friday, since I will be running this on Mondays. Is there a way to specifically call on "the previous Friday" so that if I happen to run it on Tuesday it will still pickup last Friday? I'm sure its a line of code, just not sure where to look for it. Thanks, ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569940 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having a cell equal a specific day of the week?
I think I'm looking for more of what the first response was, no necessarily a code, but something I can put in a sell sort of like =TODAY()-WEEKDAY(TODAY()-6) I'm just not sure exactly what that means and what number I have to pu in for it to equal a certain day sorry! Could someone just explain it? Thanks, ~ -- nbaj2 ----------------------------------------------------------------------- nbaj2k's Profile: http://www.excelforum.com/member.php...fo&userid=3648 View this thread: http://www.excelforum.com/showthread.php?threadid=56994 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having a cell equal a specific day of the week?
I suggest you try with several dates (instead of TODAY()) and several numbers (instead of just 6); I'm sure you'll see how it
works real soon -- Kind regards, Niek Otten Microsoft MVP - Excel "nbaj2k" wrote in message ... | | I think I'm looking for more of what the first response was, not | necessarily a code, but something I can put in a sell sort of like | | =TODAY()-WEEKDAY(TODAY()-6) | | I'm just not sure exactly what that means and what number I have to put | in for it to equal a certain day sorry! | | Could someone just explain it? | | Thanks, | | ~J | | | -- | nbaj2k | ------------------------------------------------------------------------ | nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 | View this thread: http://www.excelforum.com/showthread...hreadid=569940 | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having a cell equal a specific day of the week?
Try this formula.
It will look up the previous Friday from the date you enter in Cell A1 =IF(TEXT(A1-1,"dddd")="Friday",A1-1,IF(TEXT(A1-2,"dddd")="Friday",A1-2,IF(TEXT(A1-3,"dddd")="Friday",A1-3,IF(TEXT(A1-4,"dddd")="Friday",A1-4,IF(TEXT(A1-5,"dddd")="Friday",A1-5,IF(TEXT(A1-6,"dddd")="Friday",A1-6)))))) Good Luck Chas PS Itried it and IT does work!! "nbaj2k" wrote: I think I'm looking for more of what the first response was, not necessarily a code, but something I can put in a sell sort of like =TODAY()-WEEKDAY(TODAY()-6) I'm just not sure exactly what that means and what number I have to put in for it to equal a certain day sorry! Could someone just explain it? Thanks, ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569940 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having a cell equal a specific day of the week?
PS.
I think the coded version was easier !!!!!! Chas "ChasAA" wrote: Try this formula. It will look up the previous Friday from the date you enter in Cell A1 =IF(TEXT(A1-1,"dddd")="Friday",A1-1,IF(TEXT(A1-2,"dddd")="Friday",A1-2,IF(TEXT(A1-3,"dddd")="Friday",A1-3,IF(TEXT(A1-4,"dddd")="Friday",A1-4,IF(TEXT(A1-5,"dddd")="Friday",A1-5,IF(TEXT(A1-6,"dddd")="Friday",A1-6)))))) Good Luck Chas PS Itried it and IT does work!! "nbaj2k" wrote: I think I'm looking for more of what the first response was, not necessarily a code, but something I can put in a sell sort of like =TODAY()-WEEKDAY(TODAY()-6) I'm just not sure exactly what that means and what number I have to put in for it to equal a certain day sorry! Could someone just explain it? Thanks, ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569940 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having a cell equal a specific day of the week?
Hello Again,
Dont' bother using my formula. Use the one suggested by Neik. If you paste that in a cell it will always give you previous Friday. Nice One Neik, but I dont really understand how and why it works. I know that the 6 signifies Friday. Please explain if you dont mind ChasAA "nbaj2k" wrote: I think I'm looking for more of what the first response was, not necessarily a code, but something I can put in a sell sort of like =TODAY()-WEEKDAY(TODAY()-6) I'm just not sure exactly what that means and what number I have to put in for it to equal a certain day sorry! Could someone just explain it? Thanks, ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569940 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having a cell equal a specific day of the week?
I was wondering, is there code like the one from Niek that would allo me to also pick something from 2 Fridays back, or Two Thursdays bac and so on? I need one cell to equal one wednesday back and the other to be wednesdays back This is the code for 1 Specific day back, just not sure what I woul add to it to make it 2 specific days back (If you get what I'm saying) =TODAY()-WEEKDAY(TODAY()-6) Thanks, ~ -- nbaj2 ----------------------------------------------------------------------- nbaj2k's Profile: http://www.excelforum.com/member.php...fo&userid=3648 View this thread: http://www.excelforum.com/showthread.php?threadid=56994 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having a cell equal a specific day of the week?
Subtract another 7 (that means another 7 days)
-- Kind regards, Niek Otten Microsoft MVP - Excel "nbaj2k" wrote in message ... | | I was wondering, is there code like the one from Niek that would allow | me to also pick something from 2 Fridays back, or Two Thursdays back | and so on? | | I need one cell to equal one wednesday back and the other to be 2 | wednesdays back | | This is the code for 1 Specific day back, just not sure what I would | add to it to make it 2 specific days back (If you get what I'm saying) | | =TODAY()-WEEKDAY(TODAY()-6) | | Thanks, | | ~J | | | -- | nbaj2k | ------------------------------------------------------------------------ | nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 | View this thread: http://www.excelforum.com/showthread...hreadid=569940 | |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having a cell equal a specific day of the week?
wow, brain lapse, sorry about that, that makes complete sense not sure what I was thinking! Thanks for the help! ~J -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=569940 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2010-01-04 is equal to week 2??? | Excel Discussion (Misc queries) | |||
SumProduct by specific week in a month | Excel Discussion (Misc queries) | |||
Sum if Week Number and style is equal to any condition | Excel Discussion (Misc queries) | |||
Sum range of a week if condition is equal to.. | Excel Discussion (Misc queries) | |||
Find specific date for day in week | Excel Discussion (Misc queries) |