I have the functions in the attached file.
I have a sumproduct function with two conditions.
When the Condition is linked to column formatted as Date & Time it doesnt give the proper answer, but when the same date is formatted as decimal number it gives correct answer.
Is there are a way to build this into the function without having to create a helper column ???
Last edited by ibuhary; 02-20-2018 at 09:33 AM. Reason: Solved
What appears to be date and time in column B are actually text.
You can convert them all to numeric date and time by selecting data in column B, then use Text To Columns, selecting fixed width and eliminating the column delimiters.
Alternatively, select the data in column B, press ctrl+H (Find/Replace) and replace ":" with ":".
Re: Excel Sumproduct function linked to Date & Time not gives proper result
With PowerQuery (add-in for Ex2010 Pro Plus)
is that what you want?
I am afraid I am unable to do any changes in the column - Date & Time 1
Its coming from data sources and also linked to many things. I have room only to play in Column D
I am thinking of using something like =SUMPRODUCT(--($A$2:$A$28=A2),--(($B$2:$B$28+0)<B2)) to try to convert the data to numbers, but it doesnt work.
Last edited by ibuhary; 02-20-2018 at 09:22 AM. Reason: Adding more description
Change to:Originally Posted by ibuhary
=SUMPRODUCT(--($A$2:$A$28=A2),--($B$2:$B$28-B2<0))
When I open your file, I see a pile of #VALUE errors. in Columns B & D. This formula in C2, copied down:
=DATE((MID(B2,7,4)),(LEFT(B2,2)),(MID(B2,4,2)))+RIGHT(B2,8)+0
cures that (format as dd/mm/yyyy hh:mm). However, i have no idea what you are trying to achieve!!!
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh
Wow, thats the one I was looking for, works like a Gem.
But can you please explain me the logic behind that.
Who are you talking to?
I was trying to convert the data format to Numbers in the Destination Cell itself.
It worked what @Phuocam suggested...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks