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.

Access/Excel and the last day of the year

Discussion in 'Business Applications' started by chaosboy, Jul 2, 2006.

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

    chaosboy Thread Starter

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

    Long time since the last post - but have always found this site to be really helpful - here goes.

    I'm looking to create a formula which will recognise the gains on a portfolio, from the beginning of the past week, the beginning of the past month, and from the closing balance of the previous year, ie 31st dec 2005, have been looking on knowledge base at microsoft to no avail, so hoping you may be able to help.

    Plus be able to recognise the start of a new year automatically.

    Thanks for looking,

    CB
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    CB, are we talking Access or Excel?
    I have some code for Access for recognising the new year automatically.
    Can I be clear, are you looking for the formulae for the portfolio gains or the way to work out the week, month, year bit?
     
  3. chaosboy

    chaosboy Thread Starter

    Joined:
    May 26, 2004
    Messages:
    44
    OBP, Thanks for coming back so quickly.

    It's to set as a starting date the last working day of the previous year in either access or excel, I'm using both, so if I could find a formula for the start of the year, and then subtract a day or two, would that be possible?
    I'm looking for essentially the way to work out the week the month or the year as I need this to populate a field to then go and interrogate an sql database.

    Thanks in advance,

    CB
     
  4. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    CD
    In Excel, there are additional functions for many purposes that can be added to the normal functins available. I am not sure if you have installed these functions in Excel.
    If not you should install them. So on the basis that you may not have done that, this would be what to do

    On the Tools menu, click Add-Ins.
    In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
    If necessary, follow the instructions in the setup program.

    I am not sure anymore if you need your original software CD's - I think you do these days, if you have a more recent version of Excel.

    Once the additional functions have been installed, you can then do the following to see if any of the new functions are of any help.

    Click on a blank cell
    Click on the fx icon in the formula bar
    This will bring up the Insert Function Dialogue Box
    Click on the dropdown icon to the right of where it says - or select a category
    Click on Date and Time
    In the bottom left of the dialogue box click on - Help on this function
    You should then get the Microsoft Excel Help screen, which should have all the Date & Time functions listed on the left hand side of your screen.
    By clicking on each function, you will get all the help needed for that function.

    I have only mentioned this incase you were not aware of the additional Toolpack functions.

    If you are, then please ignore my post.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    CB, can I be clear on your requirement. Do you want say Access to automatically notify you at the end of the Week, end of the Month and End of Year, put the date in a field and then run a Query to interrogate the SQL database based on the field's value?
     
  6. chaosboy

    chaosboy Thread Starter

    Joined:
    May 26, 2004
    Messages:
    44
    OBP - Sorry about the lack of clarity, new to using this programme.....

    I don't need to be notified, but yes I do need for that date to be put in a field and then for the query to interrogate the sql database.

    Villain, thanks for the advice, I've added that pack, which no doubt at some point in the future will be useful!!

    Once again - many thanks for looking.

    CB
     
  7. 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/479900