How can I compare Excel serial dates WITHOUT converting to mm/dd/yy type dates?
- by dwwilson66
I have a table that contains a number of values representing Excel serial dates. After a number of unsuccessful attempts to compare fields, my current approach is to do comparisons between serial dates instead of calendar dates. I am trying to summarize the data--by DAY--with formulae.
CONSIDER:
41021 some data
41021.625 some data
41021.63542 some data
41022 some data
41022.26042 some data
41022.91667 some data
41023 some data
41023.375 some data
DESIRED RESULT:
41021 sum of 41021, 41021.625 and 41021.63542 data
41022 sum of 41022, 41022.26042 and 41022.91667 data
41023 sum of 41023 and 41023.375 data
In essence, for all instances of SerialDate.SerialTime, SUM data values associated with SerialDate.* regardless of the *.SerialTime for that date.
While I can see how to do this by creating additional dates column formatted as =TEXT(<DateField>,"mm/dd/yyyy") I'm looking for a solution that will allow me to handle this 'conversion' in the formula, e.g.SUMIF((TEXT(<dateRange>,"yy/mm/dd"),=(TEXT(<dateField,"yy/mm/dd")),<dataRange>
Make sense? Anyone have any ideas?
Thanks
