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

**mukeshbaviskar** · 04-13-2013, 12:49 PM

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

**FDibbins** · 04-13-2013, 05:07 PM

Hi and welcome to the forum

Your attacment didnt make it? (or you forgot to add it?)

**mukeshbaviskar** · 04-13-2013, 11:11 PM

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

**FDibbins** · 04-14-2013, 12:45 AM

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.

**mukeshbaviskar** · 04-14-2013, 03:10 AM

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