Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Access/Excel and the last day of the year


(!)

chaosboy's Avatar
chaosboy chaosboy is offline
Member with 44 posts.
THREAD STARTER
 
Join Date: May 2004
Location: London, England
Experience: Beginner
02-Jul-2006, 10:43 AM #1
Access/Excel and the last day of the year
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
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,520 posts.
 
Join Date: Mar 2005
Location: UK
02-Jul-2006, 05:22 PM #2
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?
chaosboy's Avatar
chaosboy chaosboy is offline
Member with 44 posts.
THREAD STARTER
 
Join Date: May 2004
Location: London, England
Experience: Beginner
03-Jul-2006, 03:27 PM #3
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
The Villan's Avatar
Member with 2,222 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
04-Jul-2006, 01:25 AM #4
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.

Last edited by The Villan; 04-Jul-2006 at 01:31 AM..
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,520 posts.
 
Join Date: Mar 2005
Location: UK
04-Jul-2006, 05:58 AM #5
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?
__________________
OBP
I do not give up easily
chaosboy's Avatar
chaosboy chaosboy is offline
Member with 44 posts.
THREAD STARTER
 
Join Date: May 2004
Location: London, England
Experience: Beginner
04-Jul-2006, 03:55 PM #6
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
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑