+ Reply to Thread
Results 1 to 15 of 15

Formular to subtract a number per day

  1. #1
    Registered User
    Join Date
    08-05-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    55

    Formular to subtract a number per day

    I needa formular that will subtract a number per day, for example as one day passes you can set the amount of number to be taken away from the total.
    Can any one help or tell me if this is possible

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formular to subtract a number per day

    You will need to provide more info... if for ex. you mean you want to do something along the lines of say:

    subtract say 2 from the value specified in A1 for each day passed since the date specified in B1 up to today then

    C1: =A1-2*(TODAY()-B1)

  3. #3
    Registered User
    Join Date
    08-05-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    55

    Re: Formular to subtract a number per day

    i have collected warrenty dates for my company and put the number of days left on the warrenty in cells, everyday that passes to take one off the number in the cells. So it keeps the numbers upto date.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formular to subtract a number per day

    Have you stored the warranty dates themselves ? You could then use a Formula to calculate days remaining based on current date. A sample file would help.

  5. #5
    Registered User
    Join Date
    08-05-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    55

    Re: Formular to subtract a number per day

    Quote Originally Posted by DonkeyOte View Post
    Have you stored the warranty dates themselves ? You could then use a Formula to calculate days remaining based on current date. A sample file would help.
    Not really, the website only displays the amount of dates left on the warrenty so only the amount of days are inserted. There are no dates left.
    Thats why i though there may be a formular to subtract using the computers clock.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formular to subtract a number per day

    If there is no date to compare to the computer won't know as to when a further 1 was to be subtracted from the existing values unless the file runs 24x7.

    Again, guessing having no file to work with, I would suggest you use a Workbook_Open event to compare the current date to a cell containing the last date the values were updated and if the difference > 1 day then reduce all values by the number of days having passed since last update, the date cell itself is then updated to reflect the fact that the current day calculation has been performed.

    for sake of demo. let's assume you have numerical values in B1:B100 which are to be reduced by 1 each day, in C1 we have a static date stamp to indicate when the values were last adjusted, let's initially set to say 04-Aug-2009 (yesterday).

    Please Login or Register  to view this content.
    The above would reside in ThisWorkbook Object in VBE.
    Last edited by DonkeyOte; 08-05-2009 at 06:43 AM.

  7. #7
    Registered User
    Join Date
    08-05-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    55

    Re: Formular to subtract a number per day

    Ill attach my sheet and then you can have a look cause i'm not to sure what i need to so.
    Thanks alot mate
    Attached Files Attached Files

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formular to subtract a number per day

    Yes as expected, you need to store the last date the values were updated (say F1), then run some VBA when the file opens as suggested, however, you have a bigger problem in so far as you have multiple day values within one cell (eg 113 & 843) if this can't be avoided the approach would need to be adapted from that advised previously to something along the lines of:

    Please Login or Register  to view this content.
    Again the above would reside in ThisWorkbook object model and would be invoked when the file was opened (Macros must be enabled obviously).

  9. #9
    Registered User
    Join Date
    08-05-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    55

    Re: Formular to subtract a number per day

    ok ignore those mulitple dates then, i will remove them so it only displays one number.

    I dont get the f1 but you are talking about, and i now i have togo into the developers tab and then visual basics but where to i go from there, i dont know where to insert the coding into.

    Ive just finshed a college course not a uni course lol

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formular to subtract a number per day

    Using your sample file as you uploaded (do this on a copy rather than the real thing until you're comfortable with what happens):

    Into Cell F1 on Sheet1 enter the value: 04/08/2009

    Right Click on the XL icon next to the File Menu and select View Code and into the resulting window paste the code given in my penultimate post. Save your file.

    If you now close & re-open your file (enabling Macros) you should find all the values in column D reduce by 1 and the value in F1 updates to today's date (05/08/2009) .. the values in D will only adjust on open where the date in F1 is less than today's date (ie when a catch up the reductions is required).

  11. #11
    Registered User
    Join Date
    08-05-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    55

    Re: Formular to subtract a number per day

    "
    Right Click on the XL icon next to the File Menu and select View Code and into the resulting window paste the code given in my penultimate post. Save your file."

    im usinig excel 2007, i cant seem to see these buttons

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Formular to subtract a number per day

    see attached - note I left F1 as today's date initially such that when you open the file the values do not alter initially... to re-test you would alter the value in F1 to say yesterday's date, save the file and re-open and the values should adjust accordingly.

    To reiterate it is essential that Macros are enabled in order for you to use this approach.

    EDIT: file reloaded at 13:07 UK time
    Attached Files Attached Files
    Last edited by DonkeyOte; 08-05-2009 at 08:06 AM.

  13. #13
    Registered User
    Join Date
    08-05-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    55

    Re: Formular to subtract a number per day

    i am very thankful for being patient with me, if you have some spair time can you draw me like a step by step guide cause i would really like to know how you did it. If you aint up for it then thats fine.
    THANKS FOR YOUR HELP SO FAR

  14. #14
    Registered User
    Join Date
    04-18-2022
    Location
    saudi
    MS-Off Ver
    10
    Posts
    2

    Re: Formular to subtract a number per day

    Quote Originally Posted by DonkeyOte View Post
    see attached - note I left F1 as today's date initially such that when you open the file the values do not alter initially... to re-test you would alter the value in F1 to say yesterday's date, save the file and re-open and the values should adjust accordingly.

    To reiterate it is essential that Macros are enabled in order for you to use this approach.

    EDIT: file reloaded at 13:07 UK time
    what is the formula, that subtract number daily or each 27 monthly e.g

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,051

    Re: Formular to subtract a number per day

    Quote Originally Posted by aliii View Post
    what is the formula, that subtract number daily or each 27 monthly e.g
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1