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

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
|

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

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

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
|

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

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

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

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

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
|

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