0

I am trying to compare dates in a worksheet range and am struggling to convert Today's Date into the format DD-MMM-YY (i.e. "9-Jul-25"). Can anyone help with how I need to convert this? Whether by converting to a string or by any other means.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getWorksheet("Summary)");
    let dataRange = selectedSheet.getRange("AC:AC").getUsedRange();
    let maxRows = dataRange.getRowCount()+7;
    let maxCols = dataRange.getColumnCount();

    for (let row = 0; row < maxRows; row++) {
        for (let col = 0; col < maxCols; col++) {
            let valName = dataRange.getCell(row, col).getValue();

        let dateTest = new Date(Date.now());
        const separator = "-"
        console.log(`${dateTest.getMonth()}${separator}${dateTest.getDate()}${separator}${dateTest.getFullYear()}`)
    
        if (valName == (`${dateTest.getMonth()}${separator}${dateTest.getDate()}${separator}${dateTest.getFullYear()}`) ) {
            console.log("Yes")
            }
        }
    }
}
Improve this question
5
  • 1
    Are you talking about an actual date or text that looks like a date? Commented Jul 9 at 14:44
  • 2
    Dates in both Excel and JavaScript have no format, they're binary values. How they're displayed depends on the cell's number style. Excel saves dates as a float so getValue() actually returns a number. To convert it to a date check Read an Excel Date new Date(Math.round((cell.getValue() - 25569) * 86400 * 1000)); Commented Jul 9 at 14:56
  • 2
    the format DD-MMM-YY (i.e. "9-Jul-25" that's a terrible format. You have no idea whether the year or month are first or even what year it is. You CAN'T just assume this is 2009 or 1925. You'd have to convert the float to a Date object anyway, so might as well compare Date with Date. You can use dateRange.getValues() to get a 2D array of all the column values instead of iterating. The getText() and getTexts() methods return the display text, which can vary based on the user's language Commented Jul 9 at 15:02
  • 1
    9 isn't DD, it's D. (e.g., DD looks like 08, 09, 10, while D looks like 8, 9, 10). Please edit to fix this so as not to distract from the question. Commented Jul 9 at 15:16
  • 2
    Don't confuse how a date is STORED with how a date is DISPLAYED. If you are going to compare two dates you must use them in their internal stored representation. So, a formula like =If(A1>Today(),... is the only right way to successfully compare them. If what you want instead is to display today's date in a particular format, then put the formula =Today() into a cell and format it (custom) as `D-MMM-YY'. Commented Jul 9 at 15:59
Related questions
2191
How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?
1706
How to convert a string to number in TypeScript?
825
How do I convert a string to enum in TypeScript?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.