excel help

Locked

i have an excel spredsheet to track bookings for a villa.

i have a column for booking start date and a column for booking end date.

once a date is entered in both columns (the bookings are only available in weekly blocks) is it possible to get excel to calculate the number of weeks and put that figure in a separate column??

howard60 20:31 24 Jul 04

I would have thought as they are weekly blocks something like = first date - second date / 7 would do it.

so how would i cope with a change in month, ie if the first week is at the end of august and the second is in the beginning of september??

Qdiddy 20:46 24 Jul 04

Make a list of week nos against each day in the year on a separate sheet. Use a VLookup formula to decide what week your booking is in. Then do a sum of the weeks as necessary. This won't help on partial weeks, but you could then use a combination of days 1-7 against each week no and which would show any partial weeks.

howard60 20:53 24 Jul 04

if I remember correctly excel uses a no. for each day. this is based on some day in the past being day one and each day after that is incremented by one. You may have to format your date entry to be the number format. Not too sure but will have a look at excel.

Look at it from a different perspective.

Column A to contain the start date of the booking, column B the end date and column C the number of weeks.

You would enter the start date and the number of weeks. Place the following formula in the B column and replicate down for the year

eg A1 contains the date, B1 contains:

=IF(OR(A1="",C1=""),"",(7*C1)+A1)

and C1 contains the number of weeks.

Format columns A and B to a date format

It is then easy to total column C at any time

HTH

As howard60 states you can get the weeks by using

=(B1-A1)/7

if you must enter both dates

The month element does not come into it as the date is stored as a number in the cell and it is the formatting to date that changes it to look like a date.

HTH and sorry for the delay but I keep getting a Server error