时间戳

首页 > 解决方案 > 突出显示距离 3 个月以内的日期

office-scripts - 突出显示距离 3 个月以内的日期

问题描述

我有一个简单的电子表格列出证书和到期日期。在我们将其移至在线共享点之前，我们有一个宏，而不是在打开电子表格时会检查一系列单元格中的日期并突出显示三个月内的日期。它的目的是在过期之前突出显示任何需要更新的内容。

我很欣赏宏不是 Excel Online 中的选项。这（或非常相似的东西）可以在 Office 脚本中实现吗？

标签： office-scriptsexcel-online

解决方案

应该可以创建一个 Office 脚本，突出显示日期在三个月内的单元格。

就像是：

function main(workbook: ExcelScript.Workbook)
{
  // highlight all days between today and the next number of days out
  // Number of days to look ahead
  const daysOut = 90;

  // Excel by default stores dates as the number of days after January 1, 1900
  const dayMin = currentDaysSince1900();
  const dayMax = dayMin + daysOut; 

  // Need to target the column to look at and how far down the column
  const columnToLookAt = "A";
  const rowStart = 1;
  const rowEnd = 4;

  const rangeAddress = `${columnToLookAt}${rowStart}:${columnToLookAt}${rowEnd}`;

  const sheet = workbook.getActiveWorksheet();

  // get range column
  const range = sheet.getRange("A1:A3");
  const values = range.getValues();

  // iterate through the rows of values
  for (let i =0 ; i < values.length; i++) {
    const value = values[i][0];
    console.log(value);
    if (typeof value === "number") {
      // only look at numbers
      if (value >= dayMin && value <=dayMax ) {

        // highlight
        const rangeAddress = `${columnToLookAt}${rowStart +i}`;
        const range = sheet.getRange(rangeAddress);
        range.getFormat().getFill().setColor("yellow");
      }
    } 
  }

  
}

/**
 * Current Days since Jan 1 1900
 * Equivalent to number of current excel day
 */
function currentDaysSince1900() {

  // method returns the number of milliseconds elapsed since January 1, 1970
  const nowMilliseconds = Date.now();

  const millisecondsPerDay = 24 * 60 * 60 * 1000 ;
  const nowDays = Math.floor(nowMilliseconds / millisecondsPerDay);
  const daysBetween1900And1970 = 25567;

  const elapsed = nowDays + daysBetween1900And1970 +2; // add two to include both jan 1s

  return elapsed;
}

在触发脚本方面：

  • Office 脚本当前不支持在打开工作簿时运行脚本。
  • 您可以随时手动触发脚本。
  • 您还可以创建 Microsoft Power Automate 流以每天运行脚本以保持工作簿更新。

更多 Office 脚本资源：

推荐阅读