• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Date Functions.

trianna

New Member
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
 
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.
 
Triann

How many days in the second month?

Try: =B2-DATE(YEAR(B2),MONTH(B2),0)
 
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.
 
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
 
Hui, thank you very much for your reply. I will try and revert if I would need further assistance. Thanks again.
 
Back
Top