+ Reply to Thread
Results 1 to 5 of 5

Date should not change based on cell 'c7' today function

  1. #1
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Date should not change based on cell 'c7' today function

    Dear Friends,
    I have created leave register for my office. I want to add 1 casual leave to employees account monthly on first of each month. I have created a formula for it based on cell 'c7' today function but the problem is that the date in column 'b' changes daily. I want it to be static/remain unchanged. As a result I am not getting a correct result in cell 'e8' column 'e' respectively. I have attached a sample leave register for your kind perusal. Please provide me a formula or vba macro code for solving my problem. I don't want to set maximum iteration to 1 instead I want to use vba macro code for it.
    Thanking you in anticipation.

    Mukesh

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,901

    Re: Date should not change based on cell 'c7' today function

    Hi and welcome to the forum

    Your attacment didnt make it? (or you forgot to add it?)
    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

  3. #3
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Question Re: Date should not change based on cell 'c7' today function

    Dear Fdibbins,
    Please see the attachment. I want to us safer vba macro code to solve this problem. I want to create a code to unchanged the date in column 'b' based on cell 'c7' respectively.
    Still I'm unable to attach the attachment. Please send me your personal email id so that I could mail you with a attachment and solve my problem.
    Please also guide me how to attach the file. I used upload manager but in vain.

    Mukesh

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,901

    Re: Date should not change based on cell 'c7' today function

    you still didnt attach anything?

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  5. #5
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Date should not change based on cell 'c7' today function

    Dear Fdebbins,
    Thank you for your response. I'm visually impaired person working as a teacher in high school. I will try your guidance to attach the file. I don't know whether I will success or not so I'm giving you a detail description of my register and expectations here.

    Description:
    cell 'c7' contains: current date formula
    =today()
    column 'b' contains date based on cell 'c7' range 'b8:b500' the formula is:
    =$c$7
    I don't want to change the date in column 'b' range 'b8:b500' even the date in cell 'c7' changes daily. Because I have to add 1 casual leave (cl) to each employee on every first day of each month respectively in column 'e' range 'e8:e500' respectively. my formula in column 'e' range 'e8:e500' is:
    =if(day(b8)=1,1,0)
    It's working fine if the date in column 'b' should not change daily. If it remain static/unchanged then the formula works well.

    Explanation:
    cell 'c7' contains 1/4/2013 then
    cell 'b8' should contain 1/4/2013 and should not change on next day.
    cell 'c7' contains 2/4/2013 then
    cell 'b9' contains 2/4/2013 and should not change daily on next day
    if cell 'c7' contains 3/4/2013 then
    cell 'b10' should contain 3/4/2013 and remain static and so on.

    In short the dates in column 'b' should come serially from first day to last day of each month.
    please provide me a vba safer code. I don't want to set maximum iteration to 1.

    I also want to hide the rows which has no entry (dash sign in column 'm') in column 'm' range 'm8:m500' at the end of month that is the last day of month at 6'o clock monthly. It essential for taking a printout of register and save paper, look of printout.

    Sorry I couldn't attach the file as per your guidance. Please provide your personal email id so that I will send it to you for reference if required.

    Thanking you in anticipation.

    Mukesh

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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