Difference between two dates rounded up.

M

megan

I would like to calculate the difference in two dates but in weeks. However
if the result is less than 1 week I would like the result rounded up to 1
week. Can anyone help I have had numerous attempts.
 
M

Mike

=IF(DATEDIF(A1,B1,"d")/7<1,1,DATEDIF(A1,B1,"d")/7)

Try this. Note that it will only round up the first week, weeks greater than
1 will be a decimal.

Mike
 
M

macropod

Hi Megan,

Assuming the dates are in A1 & A2, with A2 holding the later date:
=ROUNDUP((A2-A1)/7,0)
will round up the count of weeks to the next nearest week if less than a whole week. If you only want the first week rounded up,
try:
=MAX(1,ROUND((A2-A1)/7,0))

Cheers
 
M

megan

Thank you so much. That works perfectly.

Mike said:
=IF(DATEDIF(A1,B1,"d")/7<1,1,DATEDIF(A1,B1,"d")/7)

Try this. Note that it will only round up the first week, weeks greater than
1 will be a decimal.

Mike
Click to expand...
 
M

megan

Thanks. This works much better as with the other formula I was having
problems when the dates were the same. Thanks again.

macropod said:
Hi Megan,

Assuming the dates are in A1 & A2, with A2 holding the later date:
=ROUNDUP((A2-A1)/7,0)
will round up the count of weeks to the next nearest week if less than a whole week. If you only want the first week rounded up,
try:
=MAX(1,ROUND((A2-A1)/7,0))

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

megan said:
I would like to calculate the difference in two dates but in weeks. However
if the result is less than 1 week I would like the result rounded up to 1
week. Can anyone help I have had numerous attempts.
Click to expand...
Click to expand...
 
Top