SUMPRODUCT Formula works, include date format

    Morning,


    I have a formual '=SUMPRODUCT(--($E$2:E4635="NPR"),--($N$2:N4635=N4635),--($I$2:I4635<=I4635),--($I$2:I4635>=I4635-30))' which I thought worked fine, but have just noticed that the date format in cell 'I' also includes the time '31/12/2015 17:45:34'.


    When I change the date to exclude the time, the formula works fine. Looking for some advise on :


    1. Is there a way to include the date and time format into the formula?


    2. Or would I have to split the date and time into two cells?


    Any advise would be grateful.


    Cheers.

    Re: SUMPRODUCT Formula works, include date format


    Hi,


    Not sure to fully understand your question ...


    If your question relates to extracting the date, you can use =INT(yourdate) ...


    and if your want o extract the time, you can use =MOD(yourdate,1)


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: SUMPRODUCT Formula works, include date format


    Sorry, the formula I have listed works and collects the correct result when the cell has date format '01/01/2016', but for some reason its not working when the date format is '01/01/2016 05:05:05'


    And the data I have is formatted '01/01/2016 05:05:05'


    I'm guessing my formula is getting confused with the time added to the date format, was looking for a way around that.


    Cheers

    Re: SUMPRODUCT Formula works, include date format


    What you are not explaining is HOW it doesn't work. What results are you expecting, and what are you seeing that are wrong?


    My guess would be that you are seeing incorrect results where the date by itself would put the item into one category, but the addition of the time throws it into a different category.


    What you need to bear in mind is that dates and times are simply numbers. One day equals one whole number, and times are represented by fractions of the number 1 (e.g. 01:00 1:00am = 1 divided by 24). Today's date, 6 January 2016, is represented by the number 42375. Midday on Jan 6 would be represented by 42375.5.


    Your formula is taking the contents of cell I4635 and deducting 30, presumably to find items that are 30 days old. Including or excluding the time in those calculations is therefore in some instances going to impact on the result.


    If you want to ignore the time in your calculations, based on Carim's suggestion, you can use something like
    =SUMPRODUCT(--($E$2:E4635="NPR"),--(INT($N$2:N4635)=INT(N4635)),--(INT($I$2:I4635)<=INT(I4635)),--(INT($I$2:I4635)>=INT(I4635)-30))


    INT would just remove the decimal portion of the number.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login