excel '07 help

[barend]

I'm trying to modify one of my excel sheets but can't figure what formula(s) to use. I track regional fuel costs on a weekly basis for billing purposes. I want to be able to insert the first monday of each year and have the formula fill each subsequent monday within the respective month. (More specific I need the first Monday after the 25th of each month which means in some years that would be a December date) My problem lies in the fact that some months will have 5 and others only 4 such dates. This is what I have so far and I want to replace the "week #" with an actual date.

12/26/10 week 1 3.012 3.256 January week 2 3.029 3.303 week 3 3.050 3.308 week 4 3.056 3.392 week 5 3.068 3.421 total 3.043 3.336 $ 3.28 01/26/11 week 1 3.041 3.432 February week 2 3.067 3.501 week 3 3.089 3.524 week 4 3.132 3.557 week 5 total 3.082 3.504 $ 3.42 For purposes of this sample the first Monday would actually be 12/27/10 and week 5 in February should remain blank as the 2/28 falls in the next billing cycle.

 

I hope this makes sense, and more so, i hope one of you can point me in the right direction. Thanks!

Oops that table didn't transfer!!

Edited February 28, 2011 by barend
table did not paste!

[SilvrT]

I could help you with that but would need a copy of your file and a better "picture" of what you want. By "picture", I mean something you've manually created that represents the end result.

 

PM me if you want and I'll get you my email addy to send more info/file, etc.

 

Rick

[wes0778]

Try this;

Put your 1st Monday date in say A1 and format it for the format MM/DD/YR

in A2 put the formula "=A1+7" without the quote marks

in A3 put the formula "=A2+7" and do this all the way down the "A" column

To check that the next line is a monday put "=IF(A1="","",TEXT(A1, "dddd"))" in the "B" column, incrementing the line numbers all the way down.

[jer878]

Wish I could help but I'm on lesson 3 of Excel 2007 for beginners. Good news though! I have to have 12 hrs (actual hrs not credit hrs) of continuing education to meet part of my quarterly "bonus" program and after lesson 3 I have 7!!!!

[RoadKill]

I'm trying to modify one of my excel sheets but can't figure what formula(s) to use. I track regional fuel costs on a weekly basis for billing purposes. I want to be able to insert the first monday of each year and have the formula fill each subsequent monday within the respective month. (More specific I need the first Monday after the 25th of each month which means in some years that would be a December date) My problem lies in the fact that some months will have 5 and others only 4 such dates. This is what I have so far and I want to replace the "week #" with an actual date.

12/26/10 week 1 3.012 3.256 January week 2 3.029 3.303 week 3 3.050 3.308 week 4 3.056 3.392 week 5 3.068 3.421 total 3.043 3.336 $ 3.28 01/26/11 week 1 3.041 3.432 February week 2 3.067 3.501 week 3 3.089 3.524 week 4 3.132 3.557 week 5 total 3.082 3.504 $ 3.42 For purposes of this sample the first Monday would actually be 12/27/10 and week 5 in February should remain blank as the 2/28 falls in the next billing cycle.

 

I hope this makes sense, and more so, i hope one of you can point me in the right direction. Thanks!

 

Oops that table didn't transfer!!

 

Just put the 1st two dates in, then highlight both cells, left click and hold the bottom of the 2nd cell and drag down to the end of the year.

[RoadKill]

here is file.

[barend]

Thanks Roadkill, that would be the easy part which I know, but it is a tad more complicated.

 

SilverT, pm sent.

Edited March 1, 2011 by barend

[RoadKill]

If I can be any help let .me know with more requirements im pretty sure i can help. I teach and do reporting for a living

[SilvrT]

If I can be any help let .me know with more requirements im pretty sure i can help. I teach and do reporting for a living

 

Thanks... I've got his file and request and will be working on it. I also have taught Excel for close to 20 years as well as done extensive programming in it for various businesses. His requirement will involve some simple VBA coding which shouldn't take me long.