Hi

How do I calculate the following problem

If today does not equal friday then go back to last friday.

Can anyone HELP?

Regards

Winston

:(


--
Winston
------------------------------------------------------------------------
Winston's Profile: http://www.excelforum.com/member.php...o&userid=28344
View this thread: http://www.excelforum.com/showthread...hreadid=479278

Winston,
'-----------
Function GetFriday(ByRef dteEntry As Date) As String
Dim lngN As Long
lngN = Weekday(dteEntry)
If lngN < vbFriday Then
GetFriday = "The most recent Friday was " & Date - lngN - 1
Else
GetFriday = "The date entry is a Friday " & dteEntry
End If
End Function

'Call function
Sub FindTheFriday()
MsgBox GetFriday(Date) & " "
End Sub
'-----------
Jim Cone
San Francisco, USA



"Winston" wrote...
Hi
How do I calculate the following problem
If today does not equal friday then go back to last friday.
Can anyone HELP?
Regards
Winston

Hi Jim

Thanks very much for reply, but what does all this mean.

I am a new uses seems that my problem will not be sorted.

Thanks very much anyway

Winston


--
Winston
------------------------------------------------------------------------
Winston's Profile: http://www.excelforum.com/member.php...o&userid=28344
View this thread: http://www.excelforum.com/showthread...hreadid=479278

Hi Winston

I have translated Jim's code into a worksheet function. Enter in the
relevant cell:
=IF(WEEKDAY(TODAY())<6,TODAY()-WEEKDAY(TODAY())-1,TODAY())
Note: this is a volatile function, it will be recalculated everytime you
open, close, save or change the file so that calculation is triggered.

Hope this helps
Rowan

Winston wrote:
Hi Jim

Thanks very much for reply, but what does all this mean.

I am a new uses seems that my problem will not be sorted.

Thanks very much anyway

Winston

Or the shorter

=INT(TODAY()/7)*7+6


--

Regards,

Peo Sjoblom

"Rowan Drummond" wrote in message
...
Hi Winston

I have translated Jim's code into a worksheet function. Enter in the
relevant cell:
=IF(WEEKDAY(TODAY())<6,TODAY()-WEEKDAY(TODAY())-1,TODAY())
Note: this is a volatile function, it will be recalculated everytime you
open, close, save or change the file so that calculation is triggered.

Hope this helps
Rowan

Winston wrote:
Hi Jim

Thanks very much for reply, but what does all this mean.

I am a new uses seems that my problem will not be sorted.

Thanks very much anyway

Winston

Hi Peo

I get the next Friday when I try this, whereas I think the OP wanted the
previous Friday, if today is not a Friday.

Maybe
=TODAY()+CHOOSE(WEEKDAY(TODAY(),-2,-3,-4,-5,-6,0,-1)

Regards

Roger Govier


Peo Sjoblom wrote:
Or the shorter

=INT(TODAY()/7)*7+6

Hi All

Cannot believe how helpful you all are, thanks very much but.

we have a winner Rowen, I copy and pasted his formula and it the only
one that works so far?.

;) :)

Thanks Again All.

Winston.


--
Winston
------------------------------------------------------------------------
Winston's Profile: http://www.excelforum.com/member.php...o&userid=28344
View this thread: http://www.excelforum.com/showthread...hreadid=479278

Not better than Rowan's simply in answer to your question mark:

=TODAY()-WEEKDAY(TODAY(),1)+6+(WEEKDAY(TODAY())=7)*7

--
Regards,


Sandy

Replace@mailinator with @tiscali.co.uk


"Winston" wrote in
message ...

Hi All

Cannot believe how helpful you all are, thanks very much but.

we have a winner Rowen, I copy and pasted his formula and it the only
one that works so far?.

;) :)

Thanks Again All.

Winston.


--
Winston
------------------------------------------------------------------------
Winston's Profile:
http://www.excelforum.com/member.php...o&userid=28344
View this thread: http://www.excelforum.com/showthread...hreadid=479278

Hi Winston

I think that you will find that Rowan's formula will return the correct
result on 6 out of every 7 days of the week.
Try Rowan's formula when Today is 22/11/05, or 29/11/05 or any 7 day
interval after that and you will find that it returns not the previous
Friday, (21st, 28th etc,) but the Friday before that, 14th, 21st.

The formula I posted will give the correct result for all values of TODAY().


Regards

Roger Govier


Winston wrote:
Hi All

Cannot believe how helpful you all are, thanks very much but.

we have a winner Rowen, I copy and pasted his formula and it the only
one that works so far?.

;) :)

Thanks Again All.

Winston.

Hi Winston

In case you didn't see my original post it was
=TODAY()+CHOOSE(WEEKDAY(TODAY(),-2,-3,-4,-5,-6,0,-1)

Regards

Roger Govier


Roger Govier wrote:
Hi Winston

I think that you will find that Rowan's formula will return the correct
result on 6 out of every 7 days of the week.
Try Rowan's formula when Today is 22/11/05, or 29/11/05 or any 7 day
interval after that and you will find that it returns not the previous
Friday, (21st, 28th etc,) but the Friday before that, 14th, 21st.

The formula I posted will give the correct result for all values of
TODAY().


Regards

Roger Govier


Winston wrote:

Hi All

Cannot believe how helpful you all are, thanks very much but.

we have a winner Rowen, I copy and pasted his formula and it the only
one that works so far?.

;) :)
Thanks Again All.

Winston.

Typo correction...

GetFriday = "The most recent Friday was " & Date - lngN - 1
should read...
GetFriday = "The most recent Friday was " & dteEntry - lngN - 1

Jim Cone

You posted a similar but different question elsewhere, involving
the last Friday of the month, or the previous Friday before the
last day of the month, see
http://www.mvps.org/dmcritchie/excel/datecalc.htm

Actually Winston said the Friday before the last working day of the month
which is a whole new ball of worms with networkdays and holidays.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Winston" wrote in message
...

Hi

How do I calculate the following problem

If today does not equal friday then go back to last friday.

Can anyone HELP?

Regards

Winston

:(


--
Winston
------------------------------------------------------------------------
Winston's Profile: http://www.excelforum.com/member.php...o&userid=28344
View this thread: http://www.excelforum.com/showthread...hreadid=479278

Roger Govier

When I copy and paste your formula it's got a error just ends up being
text

Regards

Winston


--
Winston
------------------------------------------------------------------------
Winston's Profile: http://www.excelforum.com/member.php...o&userid=28344
View this thread: http://www.excelforum.com/showthread...hreadid=479278

Hi Winston,
FYI excelforum does not recognize threading, as your reply came
out as a reply to me rather than Roger.

There is a missing close paren after TODAY()
it should be
=TODAY()+CHOOSE(WEEKDAY(TODAY()),-2,-3,-4,-5,-6,0,-1)

You can see what a thread really looks like here.
http://google.com/groups?threadm=Win...rum-nospam.com
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Winston" wrote in message
...

Roger Govier

When I copy and paste your formula it's got a error just ends up being
text

Regards

Winston


--
Winston
------------------------------------------------------------------------
Winston's Profile: http://www.excelforum.com/member.php...o&userid=28344
View this thread: http://www.excelforum.com/showthread...hreadid=479278

Hi Winston,
That other thread you started was actually in this same newsgroup
http://groups.google.com/groups?thre...gp 13.phx.gbl

Best to keep with the orginal thread rather than causing a dilution.

Hi all,

I got enough problems with my project without losing our thread LOL :)


Winston


--
Winston
------------------------------------------------------------------------
Winston's Profile: http://www.excelforum.com/member.php...o&userid=28344
View this thread: http://www.excelforum.com/showthread...hreadid=479278

Hi David & Winston

Thank you David for pointing out the error in my posting.
Apologies Winston for the sloppiness in my typing. On this occasion, I
didn't cut and paste from the formula I had proved worked in my workbook.

I think David also posted in another thread
=TODAY()-CHOOSE(WEEKDAY(TODAY()),2,3,4,5,6,0,1)

Its amazing that one can't see that Adding negative numbers is the same as
subtracting when you are in the midst of solving a problem<vbg.

Regards

Roger Govier


David McRitchie wrote:
Hi Winston,
FYI excelforum does not recognize threading, as your reply came
out as a reply to me rather than Roger.

There is a missing close paren after TODAY()
it should be
=TODAY()+CHOOSE(WEEKDAY(TODAY()),-2,-3,-4,-5,-6,0,-1)

You can see what a thread really looks like here.
http://google.com/groups?threadm=Win...rum-nospam.com
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Winston" wrote in message
...

Roger Govier

When I copy and paste your formula it's got a error just ends up being
text

Regards

Winston


--
Winston
------------------------------------------------------------------------
Winston's Profile: http://www.excelforum.com/member.php...o&userid=28344
View this thread: http://www.excelforum.com/showthread...hreadid=479278