☰

¿Cómo puedo comparar fechas de serie de Excel SIN convertirlas a fechas de tipo mm/dd/aa?

tag-icon • tag-icon microsoft-excel worksheet-function date-time
¿Cómo puedo comparar fechas de serie de Excel SIN convertirlas a fechas de tipo mm/dd/aa?

Tengo una tabla que contiene varios valores que representan fechas de serie de Excel. Después de varios intentos fallidos de comparar campos, mi enfoque actual es hacer comparaciones entre fechas de serie en lugar de fechas del calendario. Estoy tratando de resumir los datos, por DÍA, con fórmulas.

CONSIDERAR:

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

RESULTADO DESEADO:

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

En esencia, para todas las instancias de SerialDate.SerialTime, SUMA los valores de datos asociados con SerialDate.* independientemente del *.SerialTime para esa fecha.

Si bien puedo ver cómo hacer esto creando una columna de fechas adicional formateada, =TEXT(<DateField>,"mm/dd/yyyy")estoy buscando una solución que me permita manejar esta 'conversión' en la fórmula, por ejemploSUMIF((TEXT(<dateRange>,"yy/mm/dd"),=(TEXT(<dateField,"yy/mm/dd")),<dataRange>

¿Tener sentido? ¿Alguien tiene alguna idea?

Gracias

Respuesta1

Suponiendo que sus tiempos de serie están en la columna A, los datos asociados en la columna B, la siguiente fórmula

=IF(INT($A1)=$A1,SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

mostrará la suma detrás del primer valor de tiempo entero en la columna C cuando se ingrese en C1 y se extienda a la columna. Si eso es todo lo que necesitas, ya está.

Sin embargo, si necesita todas sus sumas una debajo de la otra sin líneas en blanco entre ellas, tendrá que usar unfórmula matriciale inserte una columna adicional para las etiquetas de referencia. En la columna C, seleccione las celdas en las filas que contienen datos (es decir, si sus datos llenan las filas del 1 al 100, seleccione C1 a C100) e inserte la siguiente fórmula en la barra de fórmulas (¡no directamente en la celda!):

=IFERROR(SMALL(IF(INT($A1:$A100)=$A1:$A100,$A1:$A100,""),ROW()),"")

Tenga en cuenta que para que funcione, debe guardarlo como una fórmula matricial.confirmando la entrada de su fórmula con Ctrl+Shift+Enter(no Enter; si lo hizo bien, la fórmula se mostrará entre llaves). También tendrás que ajustar el alcance según sea necesario, ya que la fórmula devuelve 0 para las celdas vacías. Finalmente, las fórmulas de matriz de notas se vuelven bastante lentas.ooo…o…o…w en conjuntos de datos grandes.

Una vez hecho esto, puedes agregar

=IF($C1<>"",SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

a la columna D y ahí estás:

Fórmula matricial en acción

Respuesta2

Si está intentando resumir datos por día (u obtener un subtotal diario), puede probar esta fórmula matricial. Ingresa esto en la barra de fórmulas y luego presiona Ctrl+ Shift+ Enter.:

=SUM((INT(datetimes)=D3)*somedata)

Dónde

  • datetimescontiene las fechas de serie, en formato Generalomm/dd/yyyy h:mm
  • somedatacontiene algunos números para sumar (B2:B25 en mi ejemplo a continuación)

ingrese la descripción de la imagen aquí

Excel ve las fechas como números enteros (valores en serie) y las horas como decimales (una fracción de 24 horas). Por ejemplo, 06/01/2012 3:00:00 PMes igual a 41061.625(que es lo que verá si cambia el formato de celda a General). El número entero representa la fecha, mientras que 0.625representa 3:00 pmo 15:00o 15/24. Para extraer el segmento DATE, puede utilizar INT(A1).

Respuesta3

La forma más sencilla de hacerlo es utilizar las funciones integradas de tabla y tabla dinámica de Excel (2007/2010).

  1. Cree una tabla de Excel a partir de sus datos.
  2. Cree una columna auxiliar para la Fecha, utilizando la función "INT" de Excel, que trasladará todos los valores al día apropiado (la parte entera de su campo de fecha/hora). También puede crear fácilmente una columna auxiliar para el valor de Hora de su campo.
  3. Cree una tabla dinámica utilizando su tabla de datos como fuente.
  4. Configure las etiquetas de sus filas como sus fechas auxiliares y los valores dinámicos como sus puntos de datos (y puede agregar el campo Hora como etiquetas de sus columnas).

Luego, cada vez que actualice su tabla, podrá actualizar su tabla dinámica para los subtotales actuales. Como beneficio adicional, también puede elegir cualquiera de las otras funciones agregadas (mínimo, máximo, promedio) y puede optar por formatear las etiquetas de la tabla dinámica en un formato de fecha tradicional sin afectar los valores de la tabla de datos.

Si está utilizando Excel 2003, puede lograr lo mismo usando la función Lista (en lugar de Tablas).

Ejemplo Excel 2010

excel 2010

Ejemplo Excel 2003

excel 2003

información relacionada