subtracting weekending dates

I have a weekending date. I would like to subtract X amount of weeks from this date and end up with the weekending date less the weeks I subtracted.

I'm not sure were to begin.

This answer would probably help me if I knew enough about Excel! My need is to have 2 columns with different start dates and end dates and then a third column with a date 6 weeks before each start date, a due date for a deposit to be paid.

If you would be good enough to assume nothing and give me the complete formula I would be so grateful. I have spent nearly 1.5 days trying to figure it out and got nowhere!

So if you have a start date in A2 and an end date in B2 and you want C2 to give a date 6 weeks before the start date just use this formula in C2

=A2-42

daddylonglegs

Thank you very much for the reply. I have got it working – sort of. The 3rd column (because I will be entering several dates) with the calculated date fills with ######, indicating I guess that it's too narrow. When I widen it I see -12-Feb-04, ie it's worked back from the system base date. If I delete this date it deletes the formula. Is there a way of having a clear column but still with the formula?

This is what I am using: D2:D49-42 with the answer going to L2:L49.

Similarly I have a column for a deposit which is calculated from the total cost and this is filled with £0 on every row

Different question: I have a massive number of useless rows (some 65,000!) – is there a way of deleting them rather than just hiding them?

In L2 you should use just

=D2-42

...and then copy this formula down the column (you can just drag it down the required number of rows). The formula will adjust automatically, e.g. in L3 it will become =D3-42 etc.

daddylonglegs

Thank you again for your reply. I'm afraid you are assuming even the most basic knowledge. You say "In L2 you should…" – do you mean in the column, in the column heading or where? Doing it that way doesn't work for me.

I have been using the Calculator and that yields a working result, with your help, but I get the rubbish in the result Column L. As soon as I enter D2-42 in the Calculator window the Answer window fills with -12-Feb-04, and that date then appears in Column L when I press OK.

Did you have any suggestions for getting rid of 65,000 useless rows?

Apologies for my Excel ignorance. I'm really grateful for your help.

If you put the mouse pointer on cell L2 and then left-click you can just type in the formula - exactly this

=D2-42

The cell should now show a date.

You can copy this formula down the column by using this procedure.

Select L2. Place mouse pointer on the bottom right corner of the cell until you see a black +. This is the "fill handle". with the black + visible left-click the mouse and hold down. Now drag the formula as far down the column as you want.

I'm not sure why you need to get rid of the unused rows. Excel spreadsheets are always this size and many people don't utilise more than a small percentage of the entire spreadsheet, but there's no real need to do anything with the unused cells. You shouldn't need to view or print those areas, or are you getting some problems in this regard?

daddylonglegs

Thank you for your patience. I'm getting the same result all the way down L, a column of ###### or, if I widen it, the date -12-Feb-04 (with D formatted as a Date cell). When I enter a date in D I see an accurate response in L, replacing the ######. It's messy but it works and I can live with it if that's what Excel wants! It's the same with the Deposit column cells filled with £0.

I thought it would slim down the worksheet if I could delete the unwanted 65,000 rows. The document is 5.4MB, making it cumbersome if I want to email it.

I really appreciate your help – thank you.

OK,

I see what you mean - you need a way to show a blank cell when there is no date in D2.

Either change the formula in L2 to this

=IF(D2="","",D2-42)

or just use a custom format on L2

Format > Cells > Number > Custom and type this in the box

dd-mmm-yy;;

daddylonglegs

Thank you so much for your trouble.

I copied your formula into L2, dragged it down the column and it works perfectly. No clutter in the column. I've made a careful note of it!

It does feel strange that some unknown person, somewhere in the world, comes out of the ether and lends a hand to another unknown person. Many thanks.