1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel dates

Discussion in 'Business Applications' started by chaosboy, Nov 6, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. chaosboy

    chaosboy Thread Starter

    Joined:
    May 26, 2004
    Messages:
    44
    Is there any way I can get a value produced that is the previous week day, ie not a weekend, that I can then export into notepad? To be run automatically everyday?
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Your explanation is a little vague. Can you explain in detail what exactly you are trying to accomplish from start to finish? You can do so many things in Excel using VBA code so if you give a better explanation, we can help you a little more. Do you have any experience using macros?


    Rollin
     
  3. chaosboy

    chaosboy Thread Starter

    Joined:
    May 26, 2004
    Messages:
    44
    Hi Rollin,

    Every day I have to start work, and type in the same thing into MSDOS;

    h:\report\backdate.bat "workday-1"

    A number of lines is produced, (can find them out) and then I type;

    h:\report\backdate.bat

    The first line is produced by myself because we don't currently know how to search for a time sensitive file of the order of the previous days business.

    The first .bat achieves this and then the second line then "knows" what date to search for.

    So if I were able to generate the date in excel to populate the first ".bat", then the report could be automatically generated.

    If I could have a small programme that were to be able to distinguish between weekdays and weekends it would save me a hell of a lot of time.

    My experience of macros and VBA is that I know that they exist, but have never really written any.
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> if I were able to generate the date in excel

    I'm not sure where Excel fits in, from what you say. But if you have the Analysis Toolpak installed (Tools -- Add-Ins), you can use the WORKDAY function to return previous workday with eg:

    =WORKDAY(TODAY(),-1)

    HTH,
    Andy
     
  5. chaosboy

    chaosboy Thread Starter

    Joined:
    May 26, 2004
    Messages:
    44
    The reason why I'm using excel is just to produce the value for the previous workday, that can be looked for by the .bat. If it can be produced from notepad, all the better...

    Thanks for your help guys!!
     
  6. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> the value for the previous workday, that can be
    >> looked for by the .bat.

    Well, I wouldn't know. Maybe you could code it in DOS, but I haven't done any for c.10 years. What OS are you using ; 95/98? (as in 1995/98)

    Did WORKDAY in XL work for you?
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi,

    Well, if you did want to do this from within Excel (as would be my preference, but I'm biased ;) ) you may be able to use something like the following ...


    Code:
    Option Explicit
    Sub Filetest()
        Dim i As Long, d As Date, myFile As String, lastDate As Long
        lastDate = Application.WorksheetFunction.Weekday(Date)
        Select Case lastDate
        Case 1
            lastDate = lastDate - 2
        Case 7
            lastDate = lastDate - 1
        End Select
        With Application.FileSearch
            .NewSearch
            .LookIn = "H:\Report\"
            .Filename = "*" & lastDate & ".xls"
            .Execute
            For i = 1 To .FoundFiles.Count
                If FileDateTime(.FoundFiles(i)) > d Then
                    d = FileDateTime(.FoundFiles(i))
                    myFile = .FoundFiles(i)
                End If
            Next i
            MsgBox myFile
        End With
    End Sub
    As I'm not sure what your file structure is like, this routine may or may not be applicable. HTH
     
  8. chaosboy

    chaosboy Thread Starter

    Joined:
    May 26, 2004
    Messages:
    44
    Guys,

    Thanks for the suggestions, I will check at work tomorrow, and get back to you as to how it works out.

    Thanks again,

    C (y)
     
  9. chaosboy

    chaosboy Thread Starter

    Joined:
    May 26, 2004
    Messages:
    44
    All,

    Thanks for all the suggestions, but the people at work have finally come round to sort out this (what I thought would be a fairly easy bit of) programming.

    However, have picked up a book about VBA, and firefytr, you might see me on that website link of yours in the not too distant future!!!

    c
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/293086

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice