Excel-Finding no. of days between two dates 2

[itbeginner]

Hello!

I've used the following formula to calculate the number of days between 2 dates:

=ROUNDUP((C28-C27)/7,0)

Is this correct? However the result i get is not correct. The date in C27 is 30/03/2004 and that in C28 is 05/04/2004. using the above formula in cell C29, i get 1 which is completely wrong.

Can you please tell me why the formula does not work?

Can you also please tell me how to ensure that a user enters value in a cell and does not leave it blank?

 

[SkipVought]

Hi,

Dates in Excel is a NUMBER like Today is 38075.

If you subtract 2 dates you get the NUMBER OF DAYS DIFFERENCE..

You are then dividing by 7 which give you the number of WEEKS difference.




Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[fkegley]

I would use the Day function to compute the number of days between two days. =Day(A1) - Day(B1), where A1 and B1 contain the dates.

There is no easy way that I know of to REQUIRE the user to enter a value into a particular cell.

If it will always be the same cell, a macro that executes before the workbook is closed would probably be my choice. You would need to create the macro and then place an instruction in the BeforeClose event of the Workbook to cause it to execute.

If it might be different cells every time, I would probably create a custom form for data entry purposes. I would then place code in the form that ensures that something is in the cell or cells of interest before moving the data from the form to the worksheet. I have constructed a data entry macro that uses a custom form. I will be happy to let you have it for free. You can then modify it in any way you wish.



Frank kegley
fkegley@hotmail.com

 

[SkipVought]

Frank,

Please post your question in a NEW Thread.

However, DAY is NOT what you want. It is =ABS(A1-B1)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Wicca]

For me,
Like Skip said

In cell C1 put (=A1-B1)
(formatted as a number not a date)

Works for me

Merry Part and the Brightest of Blessings

With respect
Wicca
-----
IF you think you can
Or if you think you can't
Then you are probably right
-Henry Ford