dates formula

B

barachiel

Hi,

Can you please help me make a formula in getting the
results for the following:

1. 24.01.04 + 2 weeks
2. 24.01.04 + 13 days
3. 22.04.04 - 24.01.04 = no.of days
4. how many weeks from 22.04.04 to 30.11.04

Thanks

barachiel
 
N

Norman Harker

Hi Barachiel!

A1:
24-Jan-2004
B1:
=A1+14
Returns: 07-Feb-2004

A2:
24-Jan-2004
B2:
=A1+13
Returns: 06-Feb-2004

A3:
22-Apr-2004
B3:
24-Jan-2004
C3:
=A3-B3
Returns 89

A4:
30-Nov-2004
B4:
22-Apr-2004
B5:
=(A4-B4)/7
Returns: 44.71429

Or:
=INT((A4-B4)/7) [if you just want completed weeks]
Returns 44
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
J

Jebradley2003

First of all I'd have a look in the excel help files under 'How excel stores
dates and times'. Excel attriubutes a single integer to each date (on my
settings numer 1 corresponds to January 1 1900, no. 2 - Jan 2 1900 etc.24.01.04
corresponds to 38010.

With this in mind most of your problems are a bit easier.

1.)

A B C
1 24/01/04 14 (=A1+B1)


You would have to change from weeks to days - just multiply by 7. If you store
the results in a cell (like C1 above) you then change the number format in it.
Format...Cells...Number....Date. This ensures a date is returned - otherwise
the cell would probably say 38024.

2.) Same thing but cell B1 would be 13.

3.) Just treat them as if the two dates were numbers. A1 = 22.04.04, B1 =
24.01.04. C1 = A1-B1. This returns a date in C1. Just do
format...cells...number tab.....and select number in the list box on the left
hand side.

4.) Do as above then divide the answer by 7. You may want total complete weeks.
In which case use the ROUNDDOWN function.

= ROUNDDOWN((22.04.04-30.11.04)/7,0)
Again, make sure the cell format is set to number. The second argument of this
function (0) signifies the number of decimal places you want your answer
rounding down to. So for whole weeks you wouldnt want any decimal places.
If you wanted to count weeks begun but not complete then use ROUNDUP instead.

Hope this helps

Jonathan B
 
B

barachiel

Norman & Jonathan,

I'll try to apply your tips tomorrow in the office, im in
the field now.

In my query # 1 - we're just putting the no. of weeks in
column C and i'm hoping that the resulting date will
appear in column D.

A B C D
P.O. # Date Confirmed Dlvry Weeks Arrival Date
1. AF-564 24.01.2004 2 07.02.2004
2. KC-724 01.01.2004 3 22.01.2004


barachiel
 
N

Norman Harker

Hi Barachiel!

Use!
=B1+(C1*7)

Just convert the weeks to days.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

Hi Barachiel!

Thanks for thanks is always appreciated.

With date problems just remember that dates are stored as serial
numbers using 1-Jan-1900 as 1. All that is unusual is that Excel
allows you to format numbers in the date serial number range as dates.
The number stored is unaffected.

Just be a bit careful though, because time is represented by the
decimal portion of the date serial number and that can give you
problems if your date maths involves dates with time elements in them
such as NOW()

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top