Separating date and time
Posted by Grey on February 08, 2002 12:31 AM
I want manipulate to get daily totals, but am having trouble separating the date and time entries within my sumif and match formulas. Because I am working with lots of large data sets, it would be easiest if I don't have to make this into two separate columns. Does anyone know an easy way to get excel to just look at the date and not the time (even if they are in the same cell(? That is, I would like excel to see "5/11/2001 7:30:00 PM" as "5/11/2001". I have tried int() and it doesn't work. cheers for your help. grey
Posted by Matt Rowe on February 08, 2002 2:17 AM
This may sound simple, but have you tried formatting the cells as Date only?
Posted by Derek on February 08, 2002 5:19 AM
if your date/time is in A1 use =TEXT(A1,"d/mm/yy")
Posted by grey on February 08, 2002 9:23 AM
Looking for just date to then do sumif
Thanks, Matt and Derek for your suggestions. Matt - it doesn't work just to reformat as Excel still holds the hour in memory. I might be able to reformat and then paste special as values (incidentally, does anyone know a quick one-stroke key for paste special?). However, I am trying to do this without having to create more columns in my very large data worksheets (which is why Matt's suggestion is not ideal for me). Again, does anyone know how to separate the date and time within the existing column? I don't even need to keep the time. Does anyone know an easy way to get excel to just look at the date and not the time (even if they are in the same cell? That is, I would like excel to see "5/11/2001 7:30:00 PM" as "5/11/2001". I have tried int() and it doesn't work. cheers for your help. grey
Posted by Aladin Akyurek on February 08, 2002 10:49 AM
Unless some comes up with code that strips off the time component of your date and time cells, you'll need to use costly functions like in
=SUMPRODUCT((DATE(YEAR(A1:A2),MONTH(A1:A2),DAY(A1:A2))=C1)*(B1:B2))
where A1:A2 houses date and time values, B1:B2 amounts, and C1 a date (without time). The equivalent SUMIF formula would be
=SUMIF(A1:A2,C1,B1:B2) if A1:A2 had just dates.
If it isn't too much work, you could use an extra column (just inset a new column next to say A) to convert date and time cells in date cells where you apply:
=DATE(YEAR(A1),MONTH(A1),DAY(A1))
and double click the fill handle (the little square in the lower right corner of the cell) of B1 or drag B1 down with the fill handle. Copy B and do an Edit|Paste Special >Values over A then delete column B.
=========
Posted by Mark W. on February 09, 2002 4:56 PM
Why have you concluded that INT() doesn't work?...
It works for me!! The internal value for
5/11/01 19:30 is 37022.8125. =INT(37022.8125)
is 37022 -- the datevalue for 5/11/01!
Posted by Mark W. on February 09, 2002 5:07 PM
If...
...cells A1:A3 contain the datevalues for...
1/1/02 8:00 AM
1/1/02 7:00 PM
1/2/02 9:00 AM
...and their corresponding values in column B are...
{10;30;50}
...the the array formula...
{=SUM(IF(INT(A1:A3)="1/1/02"+0,B1:B3))}
...will sum the column B values associated with
1/1/02.
Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.
