Friday

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,
'-----------
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

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" <rowanzsaNotThis@hotmail.com> wrote in message
news:OrPkvcf2FHA.2492@TK2MSFTNGP09.phx.gbl...
> 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.

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.
>>
>>

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

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

--
Regards,


Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"Winston" <Winston.1xizif_1130357149.6452@excelforum-nospam.com> wrote in
message news:Winston.1xizif_1130357149.6452@excelforum-nospam.com...
>
> 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
>

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" <Winston.1xhaei_1130277942.9166@excelforum-nospam.com> wrote in message
news:Winston.1xhaei_1130277942.9166@excelforum-nospam.com...
>
> 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

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" <Winston.1xmm2e_1130526429.3179@excelforum-nospam.com> wrote in message
news:Winston.1xmm2e_1130526429.3179@excelforum-nospam.com...
>
> 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...tngp13.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

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" <Winston.1xmm2e_1130526429.3179@excelforum-nospam.com> wrote in message
> news:Winston.1xmm2e_1130526429.3179@excelforum-nospam.com...
>
>>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
>>
>
>
>