Cómo agregar formato condicional de períodos de tiempo
Escenarios de uso posibles
Usar el formato condicional de períodos de tiempo en Excel es súper útil cuando trabajas con fechas: te ayuda a seguir y gestionar visualmente los datos basados en el tiempo rápidamente.
-
ideas instantáneas sobre datos basados en el tiempo: resalta rápidamente tareas de hoy, ventas del mes pasado, fechas límite próximas, citas de la próxima semana.
-
Mejor gestión del tiempo: te ayuda a mantenerte al día con las fechas de vencimiento, eventos o elementos que caducan. Ideal para cronogramas de proyectos, facturas o horarios.
-
Actualizaciones automáticas: se actualiza dinámicamente. Si cambia la fecha de hoy, Excel actualizará automáticamente el formato sin que hagas nada.
-
Claridad visual: hace que la información sensible al tiempo destaque usando colores o estilos en negrita, para que no se pase por alto.
Cómo agregar formato condicional de períodos de tiempo en Excel
Así puedes agregar formato condicional de períodos de tiempo en Excel, muy útil para resaltar fechas como hoy, la semana pasada, el próximo mes, etc.
Pasos para agregar formato condicional de períodos de tiempo:
- Selecciona el rango de celdas de fechas que quieres formatear. Ejemplo: A2:A50.
- Ve a la pestaña Inicio en la cinta de opciones.
- Haz clic en Formato condicional en el grupo Estilos.
- Pasa el cursor sobre Reglas para resaltar celdas.
- Haz clic en Ocurrencia de una fecha…
- En la ventana de diálogo que aparece: Usa la lista desplegable para seleccionar un período de tiempo (Hoy, Ayer, Mañana, Últimos 7 días, La semana pasada, El próximo mes, etc.).
- Elige el formato (por defecto es relleno rosa claro con texto rojo oscuro, o haz clic en Formato personalizado para elegir el tuyo).
- Haz clic en Aceptar
Cómo agregar formato condicional de períodos de tiempo usando Aspose.Cells for .NET
Aspose.Cells soporta completamente el formato condicional proporcionado por Microsoft Excel 2007 y versiones posteriores en archivos XLSX en tiempo de ejecución. Este ejemplo demuestra un ejercicio para formato condicional de períodos de tiempo con diferentes conjuntos de atributos.
private void TestTimePeriod() | |
{ | |
// Instantiate a workbook object | |
Workbook book = new Workbook(); | |
// Create a worksheet object and get the first worksheet | |
Worksheet _sheet = book.Worksheets[0]; | |
AddTimePeriod_1(_sheet); | |
AddTimePeriod_2(_sheet); | |
AddTimePeriod_3(_sheet); | |
AddTimePeriod_4(_sheet); | |
AddTimePeriod_5(_sheet); | |
AddTimePeriod_6(_sheet); | |
AddTimePeriod_7(_sheet); | |
AddTimePeriod_8(_sheet); | |
AddTimePeriod_9(_sheet); | |
AddTimePeriod_10(_sheet); | |
book.Save(filePath + "TimePeriodType.xlsx"); | |
} | |
// This method implements the TimePeriod conditional formatting type with Yesterday attribute. | |
private void AddTimePeriod_10(Worksheet _sheet) | |
{ | |
FormatConditionCollection conds = GetFormatCondition("I19:K20", Color.MediumSeaGreen, _sheet); | |
int idx = conds.AddCondition(FormatConditionType.TimePeriod); | |
FormatCondition cond = conds[idx]; | |
cond.Style.BackgroundColor = Color.Pink; | |
cond.Style.Pattern = BackgroundType.Solid; | |
cond.TimePeriod = TimePeriodType.Yesterday; | |
Cell c = _sheet.Cells["I19"]; | |
Style style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c.PutValue(DateTime.Parse("2008/07/30")); | |
c = _sheet.Cells["K20"]; | |
c.PutValue(DateTime.Parse("2008/08/03")); | |
style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c = _sheet.Cells["I20"]; | |
c.PutValue("Yesterday"); | |
} | |
// This method implements the TimePeriod conditional formatting type with Tomorrow attribute. | |
private void AddTimePeriod_9(Worksheet _sheet) | |
{ | |
FormatConditionCollection conds = GetFormatCondition("I17:K18", Color.MediumPurple, _sheet); | |
int idx = conds.AddCondition(FormatConditionType.TimePeriod); | |
FormatCondition cond = conds[idx]; | |
cond.Style.BackgroundColor = Color.Pink; | |
cond.Style.Pattern = BackgroundType.Solid; | |
cond.TimePeriod = TimePeriodType.Tomorrow; | |
Cell c = _sheet.Cells["I17"]; | |
Style style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c.PutValue(DateTime.Parse("2008/08/01")); | |
c = _sheet.Cells["K18"]; | |
c.PutValue(DateTime.Parse("2008/08/03")); | |
style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c = _sheet.Cells["I18"]; | |
c.PutValue("Tomorrow"); | |
} | |
// This method implements the TimePeriod conditional formatting type with ThisWeek attribute. | |
private void AddTimePeriod_8(Worksheet _sheet) | |
{ | |
FormatConditionCollection conds = GetFormatCondition("I15:K16", Color.MediumOrchid, _sheet); | |
int idx = conds.AddCondition(FormatConditionType.TimePeriod); | |
FormatCondition cond = conds[idx]; | |
cond.Style.BackgroundColor = Color.Pink; | |
cond.Style.Pattern = BackgroundType.Solid; | |
cond.TimePeriod = TimePeriodType.ThisWeek; | |
Cell c = _sheet.Cells["I15"]; | |
Style style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c.PutValue(DateTime.Parse("2008/07/28")); | |
c = _sheet.Cells["K16"]; | |
c.PutValue(DateTime.Parse("2008/08/03")); | |
style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c = _sheet.Cells["I16"]; | |
c.PutValue("ThisWeek"); | |
} | |
// This method implements the TimePeriod conditional formatting type with ThisMonth attribute. | |
private void AddTimePeriod_7(Worksheet _sheet) | |
{ | |
FormatConditionCollection conds = GetFormatCondition("I13:K14", Color.MediumBlue, _sheet); | |
int idx = conds.AddCondition(FormatConditionType.TimePeriod); | |
FormatCondition cond = conds[idx]; | |
cond.Style.BackgroundColor = Color.Pink; | |
cond.Style.Pattern = BackgroundType.Solid; | |
cond.TimePeriod = TimePeriodType.ThisMonth; | |
Cell c = _sheet.Cells["I13"]; | |
Style style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c.PutValue(DateTime.Parse("2008/07/5")); | |
c = _sheet.Cells["K14"]; | |
c.PutValue(DateTime.Parse("2008/05/30")); | |
style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c = _sheet.Cells["I14"]; | |
c.PutValue("ThisMonth"); | |
} | |
// This method implements the TimePeriod conditional formatting type with NextWeek attribute. | |
private void AddTimePeriod_6(Worksheet _sheet) | |
{ | |
FormatConditionCollection conds = GetFormatCondition("I11:K12", Color.MediumAquamarine, _sheet); | |
int idx = conds.AddCondition(FormatConditionType.TimePeriod); | |
FormatCondition cond = conds[idx]; | |
cond.Style.BackgroundColor = Color.Pink; | |
cond.Style.Pattern = BackgroundType.Solid; | |
cond.TimePeriod = TimePeriodType.NextWeek; | |
Cell c = _sheet.Cells["I11"]; | |
Style style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c.PutValue(DateTime.Parse("2008/08/5")); | |
c = _sheet.Cells["K12"]; | |
c.PutValue(DateTime.Parse("2008/07/30")); | |
style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c = _sheet.Cells["I12"]; | |
c.PutValue("NextWeek"); | |
} | |
// This method implements the TimePeriod conditional formatting type with NextMonth attribute. | |
private void AddTimePeriod_5(Worksheet _sheet) | |
{ | |
FormatConditionCollection conds = GetFormatCondition("I9:K10", Color.Maroon, _sheet); | |
int idx = conds.AddCondition(FormatConditionType.TimePeriod); | |
FormatCondition cond = conds[idx]; | |
cond.Style.BackgroundColor = Color.Pink; | |
cond.Style.Pattern = BackgroundType.Solid; | |
cond.TimePeriod = TimePeriodType.NextMonth; | |
Cell c = _sheet.Cells["I9"]; | |
Style style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c.PutValue(DateTime.Parse("2008/08/25")); | |
c = _sheet.Cells["K10"]; | |
c.PutValue(DateTime.Parse("2008/07/30")); | |
style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c = _sheet.Cells["I10"]; | |
c.PutValue("NextMonth"); | |
} | |
// This method implements the TimePeriod conditional formatting type with LastWeek attribute. | |
private void AddTimePeriod_4(Worksheet _sheet) | |
{ | |
FormatConditionCollection conds = GetFormatCondition("I7:K8", Color.Linen, _sheet); | |
int idx = conds.AddCondition(FormatConditionType.TimePeriod); | |
FormatCondition cond = conds[idx]; | |
cond.Style.BackgroundColor = Color.Pink; | |
cond.Style.Pattern = BackgroundType.Solid; | |
cond.TimePeriod = TimePeriodType.LastWeek; | |
Cell c = _sheet.Cells["I7"]; | |
Style style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c.PutValue(DateTime.Parse("2008/07/25")); | |
c = _sheet.Cells["K8"]; | |
c.PutValue(DateTime.Parse("2008/07/30")); | |
style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c = _sheet.Cells["I8"]; | |
c.PutValue("LastWeek"); | |
} | |
// This method implements the TimePeriod conditional formatting type with LastMonth attribute. | |
private void AddTimePeriod_3(Worksheet _sheet) | |
{ | |
FormatConditionCollection conds = GetFormatCondition("I5:K6", Color.Linen, _sheet); | |
int idx = conds.AddCondition(FormatConditionType.TimePeriod); | |
FormatCondition cond = conds[idx]; | |
cond.Style.BackgroundColor = Color.Pink; | |
cond.Style.Pattern = BackgroundType.Solid; | |
cond.TimePeriod = TimePeriodType.LastMonth; | |
Cell c = _sheet.Cells["I5"]; | |
Style style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c.PutValue(DateTime.Parse("2008/06/26")); | |
c = _sheet.Cells["K6"]; | |
c.PutValue(DateTime.Parse("2008/07/30")); | |
style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c = _sheet.Cells["I6"]; | |
c.PutValue("LastMonth"); | |
} | |
// This method implements the TimePeriod conditional formatting type with Last7Days attribute. | |
private void AddTimePeriod_2(Worksheet _sheet) | |
{ | |
FormatConditionCollection conds = GetFormatCondition("I3:K4", Color.LightSteelBlue, _sheet); | |
int idx = conds.AddCondition(FormatConditionType.TimePeriod); | |
FormatCondition cond = conds[idx]; | |
cond.Style.BackgroundColor = Color.Pink; | |
cond.Style.Pattern = BackgroundType.Solid; | |
cond.TimePeriod = TimePeriodType.Last7Days; | |
Cell c = _sheet.Cells["I3"]; | |
Style style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c.PutValue(DateTime.Parse("2008/07/26")); | |
c = _sheet.Cells["K4"]; | |
c.PutValue(DateTime.Parse("2008/08/30")); | |
style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c = _sheet.Cells["I4"]; | |
c.PutValue("Last7Days"); | |
} | |
// This method implements the TimePeriod conditional formatting type with Today attribute. | |
private void AddTimePeriod_1(Worksheet _sheet) | |
{ | |
FormatConditionCollection conds = GetFormatCondition("I1:K2", Color.LightSlateGray, _sheet); | |
int idx = conds.AddCondition(FormatConditionType.TimePeriod); | |
FormatCondition cond = conds[idx]; | |
cond.Style.BackgroundColor = Color.Pink; | |
cond.Style.Pattern = BackgroundType.Solid; | |
cond.TimePeriod = TimePeriodType.Today; | |
Cell c = _sheet.Cells["I1"]; | |
Style style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c.PutValue(DateTime.Today); | |
c = _sheet.Cells["K2"]; | |
c.PutValue(DateTime.Parse("2008/07/30")); | |
style = c.GetStyle(); | |
style.Number = 30; | |
c.SetStyle(style); | |
c = _sheet.Cells["I2"]; | |
c.PutValue("Today"); | |
} | |
// This method adds formatted conditions. | |
private FormatConditionCollection GetFormatCondition(string cellAreaName, Color color, Worksheet _sheet) | |
{ | |
// Adds an empty conditional formattings | |
int index = _sheet.ConditionalFormattings.Add(); | |
// Get the formatted conditions | |
FormatConditionCollection formatConditions = _sheet.ConditionalFormattings[index]; | |
// Get the cell area calling the custom GetCellAreaByName method | |
CellArea area = GetCellAreaByName(cellAreaName); | |
// Add the formatted conditions cell area. | |
formatConditions.AddArea(area); | |
// Call the custom FillCell method | |
FillCell(cellAreaName, color, _sheet); | |
// Return the formatted conditions | |
return formatConditions; | |
} | |
// This method specifies the cell shading color for the conditional formattings cellarea range. | |
private void FillCell(string cellAreaName, Color color, Worksheet _sheet) | |
{ | |
CellArea area = GetCellAreaByName(cellAreaName); | |
int k = 0; | |
for (int i = area.StartColumn; i <= area.EndColumn; i++) | |
{ | |
for (int j = area.StartRow; j <= area.EndRow; j++) | |
{ | |
Cell c = _sheet.Cells[j, i]; | |
if (!color.IsEmpty) | |
{ | |
Style s = c.GetStyle(); | |
s.ForegroundColor = color; | |
s.Pattern = BackgroundType.Solid; | |
c.SetStyle(s); | |
} | |
// Set some random values to the cells in the cellarea range | |
int value = j + i + k; | |
c.PutValue(value); | |
k++; | |
} | |
} | |
} | |
// This method specifies the CellArea range (start row, start col, end row, end col etc.) | |
// For the conditional formatting | |
internal static CellArea GetCellAreaByName(string s) | |
{ | |
CellArea area = new CellArea(); | |
string[] strCellRange = s.Replace("$", "").Split(':'); | |
int column; | |
CellsHelper.CellNameToIndex(strCellRange[0], out area.StartRow, out column); | |
area.StartColumn = column; | |
if (strCellRange.Length == 1) | |
{ | |
area.EndRow = area.StartRow; | |
area.EndColumn = area.StartColumn; | |
} | |
else | |
{ | |
CellsHelper.CellNameToIndex(strCellRange[1], out area.EndRow, out column); | |
area.EndColumn = column; | |
} | |
return area; | |
} |