Friday

  • Thread starter Thread starter Winston
  • Start date Start date
W

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

:(
 
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
 
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
 
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
 
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
 
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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk
 
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
 
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
 
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.
 
Roger Govier

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

Regards

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 said:
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
Click to expand...
 
Back
Top