barend Posted February 28, 2011 Share #1 Posted February 28, 2011 (edited) 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! Link to comment Share on other sites More sharing options...
SilvrT Posted February 28, 2011 Share #2 Posted February 28, 2011 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 Link to comment Share on other sites More sharing options...
wes0778 Posted February 28, 2011 Share #3 Posted February 28, 2011 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. Link to comment Share on other sites More sharing options...
jer878 Posted February 28, 2011 Share #4 Posted February 28, 2011 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!!!! Link to comment Share on other sites More sharing options...
RoadKill Posted March 1, 2011 Share #5 Posted March 1, 2011 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. Link to comment Share on other sites More sharing options...
RoadKill Posted March 1, 2011 Share #6 Posted March 1, 2011 here is file. Link to comment Share on other sites More sharing options...
barend Posted March 1, 2011 Author Share #7 Posted March 1, 2011 (edited) 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 Link to comment Share on other sites More sharing options...
RoadKill Posted March 1, 2011 Share #8 Posted March 1, 2011 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 Link to comment Share on other sites More sharing options...
SilvrT Posted March 1, 2011 Share #9 Posted March 1, 2011 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now