Excel Date Functions.

[trianna]

Hello Everyone,


I'm hoping that someone can help me solve my date function issues.


I'm working on a spread sheet that will ultimately feed a pivot table report. The issue is I have two columns of dates the first column would be known as the Delivery Start Date and the second the Delivery end date. We need to determine the total number of days between the Start and Stop, that has been easy to do using the =DATEDIF(A2,B2,"D")+1 function. If the time between the dates carries from one month to the next, I must figure out how many days in the first month and how many in the second month. I can find the first month using =(DATE(YEAR(A2),MONTH(A2)+1,1)-A2)but can't anything to work for the second month.


Any help, thoughts or ideas are gratefully welcomed, I am over the limit at this point.


Thank you,


Triann_a

 

[Luke M]

You should be able to just do:

=B2-A2+1


I'm assuming you added 1 day to your first formula because you wanted to count the start day as 1 day. If your start & end times contain dates and you only want a whole number, a quick rememedy of:

=INT(B2-A2)+1


should work.

 

[Hui]

Triann

How many days in the second month?

Try: =B2-DATE(YEAR(B2),MONTH(B2),0)

 

[BADRI]

I am business development guy, in our BD tracking sheet there are two columns 1. First Contact made (say 18.04.2011) 2. Next contact to be made (say 23.04.2011). If this field column 2 is not updated beyond 23.04.2011 (i.e. no contact made or no next date entered in) can something be done to highlight that cell - by way of changed cell colour or red coloured font etc.? I use excel 2003.

 

[Hui]

Use Conditional Formatting

Select b2:Bx

Conditional formatting

With a formula

=B2>$C$2

or =B2>Date(2011,04,23)

set Color

Apply


If you use the formula put 23/4/2011 in C2 or else whereever else is convienient

 

[BADRI]

Hui, thank you very much for your reply. I will try and revert if I would need further assistance. Thanks again.