25 days previous to today?

Hi,

Can anyone help?

Is it possible to write a formula whereby it will calculate 25 weekdays
previous to today? I have tried to do the following, however it includes
saturdays and sunday too and i cannot work out how to avoid including
weekends?

=today()-25

Hi

Try this:
=WORKDAY(TODAY(),-25)
I believe this function requires the analysis toolpak as an add-in (see
Tools/Add-ins)

Andy.

"MaQ" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Can anyone help?
>
> Is it possible to write a formula whereby it will calculate 25 weekdays
> previous to today? I have tried to do the following, however it includes
> saturdays and sunday too and i cannot work out how to avoid including
> weekends?
>
> =today()-25

If TODAY will always be a weekday, couldn't you just use this?:

=TODAY()-5*7

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"MaQ" wrote:

> Hi,
>
> Can anyone help?
>
> Is it possible to write a formula whereby it will calculate 25 weekdays
> previous to today? I have tried to do the following, however it includes
> saturdays and sunday too and i cannot work out how to avoid including
> weekends?
>
> =today()-25

This should do it.

=WORKDAY(TODAY(),-25)

HTH

Steve

=WORKDAY(TODAY(),-25)

"Ron Coderre" wrote:

> If TODAY will always be a weekday, couldn't you just use this?:
>
> =TODAY()-5*7
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "MaQ" wrote:
>
> > Hi,
> >
> > Can anyone help?
> >
> > Is it possible to write a formula whereby it will calculate 25 weekdays
> > previous to today? I have tried to do the following, however it includes
> > saturdays and sunday too and i cannot work out how to avoid including
> > weekends?
> >
> > =today()-25

Hi Ron,

I saw you recent code posting to Samer on Goolges'
microsoft.public.excel.programming group which Find text in a string
that matches value(s) in a range and if it does find something then it
puts the text string in the cell and if it doesn't then it puts "No
Match". The code was very good.

However, is it possible to modify it to show the position of the text
string in the range of cells. For instance let say I have the following
text starting in range A1:A5

Yellow Orange
Green Grape
Blue Berries
Red Apple
Orange Grapefruit

I find to find the position of the text string "Berries". in the Range
A1:A5. The result I am looking for should produce 3, as it is located
in teh 3rd row in the range. I do not care about the position of the
actual text "Berries" in the entire string "Blue Berries" just the
position of the in the range.

How would I modify the code below to do this?

'----Start of Code-----
Option Explicit

Public Function ShowMatch(CellRef As Range, SrchVals As Range) As
String

Dim iCtr As Integer
Dim cCell As Range
Dim strTestVal As String
Dim lngMatchRef As Long
Dim Bullpen As String
Dim varFindRef As Variant

'Check if target range is only one cell and has contents
If CellRef.Cells.Count > 1 Then
ShowMatch = "#InvalidCellRef"
Exit Function
ElseIf Len(CellRef) = 0 Then
'ElseIf Application.WorksheetFunction.IsBlank(CellRef) Then
ShowMatch = "#NoMatch"
Exit Function
End If

'Check if the list to be matched is one dimensional
If SrchVals.Areas.Count > 1 Then
ShowMatch = "#TooManyListRngs!"
Exit Function
ElseIf SrchVals.Rows.Count = 1 Or SrchVals.Columns.Count = 1 Then
'Range is one-dimensional
Else
ShowMatch = "#ListRngNot1Dim!"
Exit Function
End If

lngMatchRef = 99999

'Loop through list values
For Each cCell In SrchVals.Cells
strTestVal = CStr(cCell.Value)

If Len(strTestVal) <> 0 Then
'List ref cell is not blank, so test the cell
varFindRef = InStr(1, CellRef, strTestVal)
If varFindRef > 0 Then
'The list value was found...check it's position in the cell
If varFindRef < lngMatchRef Then
'Use this list value as the first matched value
lngMatchRef = varFindRef
Bullpen = strTestVal
End If
End If
End If
Next cCell
If lngMatchRef = 99999 Then
ShowMatch = "No Match"
Else
ShowMatch = Bullpen
End If
End Function
'----End of Code-----


I would greatly appreciate your help, I am not that good at VBA and
this could save me hundreds of hours of manual data manipulation.

Regards,


Brandon
Email: [email protected]