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
=TODAY()-WEEKDAY(TODAY()-6)
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"nbaj2k" <[email protected]> wrote in message
news:[email protected]...
|
| 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
|
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
>
>
=TODAY()-WEEKDAY(TODAY()-6)
Thats the code I was looking for, just had a little brain lapse, couldn't figure out what the 6 was, I'm back with it though and figured it out!
Thanks,
~J
Last edited by nbaj2k; 08-09-2006 at 02:53 PM.
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" <[email protected]> wrote in message
news:[email protected]...
|
| 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
|
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
>
>
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
> >
> >
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
>
>
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
Subtract another 7 (that means another 7 days)
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"nbaj2k" <[email protected]> wrote in message
news:[email protected]...
|
| 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
|
wow, brain lapse, sorry about that, that makes complete sense not sure what I was thinking!
Thanks for the help!
~J
One more thing, I have been using the formula, but I now want to use it a little differently. I have 2 cells next to each other, I want one to equal the past Monday, and the other to equal the previous Friday. The thing is I need them both to be part of the same week.
For example, today is Thursday August 24th.
I have the start day set to =TODAY()-WEEKDAY(TODAY()-2) and the end date set to =TODAY()-WEEKDAY(TODAY()-6)
The problem with that is, there is a Monday in this week that went by so the range is, so the range is 8/21/06 to 8/18/06 which is not possible.
Is there a way to do it for the complete week so that it would set it to the previous week's Monday and Friday?
Sorry to reopen this!
Thanks,
~J
One more thing, I have been using the formula, but I now want to use it a little differently. I have 2 cells next to each other, I want one to equal the past Monday, and the other to equal the previous Friday. The thing is I need them both to be part of the same week.
Assume the formula in b2=TODAY()-WEEKDAY(TODAY()-6)
then a2 =b2-WEEKDAY(b2-2)
this makes the a2 cell always be before the b2 cell
Just make A2
=B2-4
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks