TimeSheet calculating an unwanted extra day on the first period... 5

[YNOTU]

Hello all,


Here's the deal H7 holds a date 07/01/2007

F10 = H7
F11 =F10+1
F12 =F11+1
AND SO ON..
.
.
.
.
F25 =F24+1

Our time periods runs from the first to the 15th and from the 16th to the 31st

If I type in H7 08/01/2007 the date populates all the way to 08/16/07 (which should only populate to 08/15/07)

but if I type in H7 08/16/07 the date populates correctly to the 31st.


The question is: How can I prevent the first period NOT to print the 16th


Thanks in advanced

 

[YNOTU]

I also tried putting a condition on cell F25 which contains formula =F24+1 to the effect of if it contains /16/ populate nothing but it did not work


I'm stumped

 

[bluedragon2]

You have 16 rows populating dates. Therefore, when you put 1/1 in H7, it populates 1/1 - 1/16 (16 dates). When you put 1/16 in H7, it populates 1/16 - 1/31 (16 dates).

You can put something in F25 to look for 1/16 like:

=IF(DAY(H7)=1,"",F24+1)

But, what about months with 28-30 days...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[yogia]

HelloTony:

You can use the following formula in cell F25 ...

=IF(DAY(H7)=1,"",F24+1)

With the preceding formula in cell F25, when you enter 8/1/2007 in cell H7, cells F10 through F24 will show 8/1/2007 through 8/15/2007, and cell F25 will be a formula blank.

Next, with the entry in cell H7 for 16th day of a month, what about the months that have less than 31 days in the month?

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[onedtent]

=IF(DAY(H7)=1,"",IF(MONTH(F24+1)>MONTH(F24),"",F24+1))
will take care of both - 16th day & 30 day month

- onedtent Onedtent OnedTent

 

[YNOTU]

Thank you both for your quick answers. I'm being told that on months that have 28 days or 30 the period ends on that day and it does not overlap

the formula you guys gave me worked great.

 

[YNOTU]

Thanks onedtent. that was awesome

 

[YNOTU]

onedtent, can I just adjust your code

=IF(DAY(H7)=1,"",IF(MONTH(F24+1)>MONTH(F24),"",F24+1))

and apply it to 28 and 30 days?

 

[onedtent]

a little more complex than that

F23 --> =IF(MONTH(F22+1)>MONTH(F22),"",F22+1)

F24 --> =IF(F23="","",IF(MONTH(F23+1)>MONTH(F23),"", F23+1))

F25 --> =IF(F24="","",IF(DAY(H7)=1,"",IF(MONTH(F24+1)>MONTH(F24),"",F24+1)))


- onedtent Onedtent OnedTent

 

[YNOTU]

thank you very much onedtent, I will try it a bit later and will post a response if I encounter issues.

Thanks again and much appreciate your time.

 

[yogia]

HelloTony:

In the following illustration, I have used one formula in cell F10 and that formula is then copied to cells F11 to F25 ...

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[YNOTU]

thank you yogia, may I ask that you write the code in the a code box to make it easier to apply the code?

Thanks

 

[yogia]

HelloTony:

I think you are refering to the development of the formula. I have shown the formula in a separate box for clarity.

In illustration of my last post, cells F10 through F25 show the result from application of the formula shown for cell F10 -- then that formula is copied down to cells F11 through F25.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com