Friday

[Winston]

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

 

[Jim Cone]

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



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

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 :rolleyes

 

[Rowan Drummond]

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

 

[Peo Sjoblom]

Or the shorter

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

 

[Roger Govier]

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

 

[Winston]

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.

 

[Sandy Mann]

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

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

--
Regards,


Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Roger Govier]

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

 

[Roger Govier]

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

 

[Jim Cone]

Typo correction...

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

Jim Cone

 

[David McRitchie]

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.

 

[Winston]

Roger Govier

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

Regards

Winston

 

[David McRitchie]

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=Winston.1xmm2e_1130526429.3179@excelforum-nospam.com

 

[David McRitchie]

Hi Winston,
That other thread you started was actually in this same newsgroup
http://groups.google.com/groups?threadm=##[email protected]

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

 

[Winston]

Hi all,

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


Winsto

 

[Roger Govier]

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

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=Winston.1xmm2e_1130526429.3179@excelforum-nospam.com