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.

is there a date formula?

Discussion in 'Business Applications' started by AR1Redneck, Dec 19, 2002.

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

    AR1Redneck Thread Starter

    Joined:
    Dec 13, 2002
    Messages:
    206
    Hello all, is there a date/time formula or a macro i can setup. So that when a certain date hits, excel will automatically substract a amount from a certain cell?

    For exmaple, if a customer buys a certain amount of supplies, on the same day of every month, lets say the 15th. In the spreadsheet when that date hits, can it automatically debit their account, rather than me doing it manually everytime?

    Thanks
    Drew
     
  2. AR1Redneck

    AR1Redneck Thread Starter

    Joined:
    Dec 13, 2002
    Messages:
    206
    bump, for anyone who hasn't seen it that might be of some help, thanks.

    Drew
     
  3. Slithy Tove

    Slithy Tove Guest

    I'm sure this could be done by a competent Excel programmer - I'm not one of them. However, this could be a dangerous business practice... (1) prices could change; (2) if the order changes even slightly you may have a lot of "un-do" work; (3) if purchase is made on credit and the clock starts ticking on a specified date but the order is not picked up on that date the credit terms may get messed up (pickup date could fall on Sunday/holiday). I would suggest that you should perform some kind of verification before you automatically charge a good and regular customer - you don't want to lose him/her.
     
  4. AR1Redneck

    AR1Redneck Thread Starter

    Joined:
    Dec 13, 2002
    Messages:
    206
    The price wont change and the date will always be the same as long as they stay with the company. We provide them with a service every month and they agreed to pay the same amount each month on a certain day. Instead of me inserting the data everyday by hand, is there a date formula or a macro that can be made so it will do it for me?

    Thanks
    Drew
     
  5. AbvAvgUser

    AbvAvgUser

    Joined:
    Oct 3, 2002
    Messages:
    2,281
    I am not an expert in excel, but here is what I think you can do. I think you will have to use VBA (Visual Basic for Application) here and write functions/macros. You need to do the coding.

    One of the cells in your WorkSheet must have a function called NOW(). So that everytime you open your .xls file, this cell will contain current date and/or time.

    Now depending on your needs, you will have to trap the events (through VBA) that get triggered. Whenever the event gets triggered (most likely in your case WorkBook.Open or WorkBook.Activate), check the date in the cell that contains the current date. If that is the date that should change the values, change them through the VBA code.

    Following are events of WorkBook and WorkSheet that may be of some use to you.

    WorkBook :
    Open
    Activate
    Deactivate
    BeforeClose
    BeforeSave
    BeforePrint
    WindowActivate
    WindowDeactivate

    WorkSheet :
    Activate
    Deactivate
    Change
     
  6. nczman

    nczman

    Joined:
    Dec 12, 2000
    Messages:
    310
    the problem with checking it every time that you open it for the 15th, is what if the 15 you are not there? holiday or such.. you would have to change your computer date to the 15th.

    Personally, what you are doing works better either in access or straight VB. Then you can use task manager to schedule it to run on the 15th, you just have to leave your computer on all of the time.

    I am not sure, but it might even be able to be done in Windows Scripting. Then again you could add that to task manager to run on the 15th.
     
  7. AR1Redneck

    AR1Redneck Thread Starter

    Joined:
    Dec 13, 2002
    Messages:
    206
    I dont know anything about access or VB.

    Drew
     
  8. AbvAvgUser

    AbvAvgUser

    Joined:
    Oct 3, 2002
    Messages:
    2,281
    Whatever nczman said is perfectly right. I missed out on that possibility. If you want to use the alternative that I suggested, you will have to switch on your machine every day at least once or change the system date and reopen the sheet.

    Also you will have to keep a check that the quantity doesn't get deducted twice on the same day if you reopen the sheet again on the same day. There may be many more checks that you need to keep and you need to have that done in VBA

    Is it possible to consult some programmer around in your city/locality to program it for you? VB and MS-Access seems to be right combination to solve this problem.
     
  9. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    When you say debit their account/subtract an amount from a certain cell - what exactly do you need?

    What is the result of that cell? Do you need it to be the $10 you want to bill him - or will it be the total of his account after subtracting $10? Is that cell then used to generate an invoice amount or just trigger a standard amount to be invoiced?

    If you just need to keep a running total of his account and have it debitted on a recurring date with a ste amount you can accomplish that without having to trigger a certain date - only pass that date. That way it won't be an event that could get duplicated.

    Fill me in a bit - I hope what I wrote wasn't totalling cornfusing!
    :confused:
     
  10. AR1Redneck

    AR1Redneck Thread Starter

    Joined:
    Dec 13, 2002
    Messages:
    206
    Yes castleheart, is a running balance of his account. The way i have it setup now, whihc is easy but a hassle sometimes. You have 5 rows. The first row is for the date, the next one is the purchase item, the next is the debit row and the following is a credit row, and then you have the balance row. I have a formula in there now that when i place a # in the credit row, it will automatically add it to this total and vice versa for the debit. Hope maybe that clewars it up.
     
  11. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    See if this example helps you out, Red.
    If it steers you in the right direction let me know if you have other questions. They are all kinds of ways to whip EXCEL and make it do things you probably ought to be doing in ACCESS. But I beat it all the time!

    :cool:

    oops! I now find that though we speak of EXCEL, it is not among the list of acceptable attachments. :mad: Give me an E address and I'll send it to you!
     
  12. AR1Redneck

    AR1Redneck Thread Starter

    Joined:
    Dec 13, 2002
    Messages:
    206
    you have a PM
     
  13. 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/109121

  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