Return to scripts

Getting a real date from a fuzzy date (to use in Calendars & Gantt Charts)

Creator: Victoria Plummer

There are certain dates that I want to estimate, but as I get closer to the date I want to visualize and edit further on Airtable’s Calendar View or Gantt Chart. The Calendar View and Gantt Charts Block only accept date fields or formula fields that calculate dates. However, I still want to retain the ability to move these dates around on the calendar/Gantt if something comes up.

First I want to structure my table correctly so that I can actually calculate my fuzzy date. I also have a field in my tasks table for “Months Out,” which basically is asking how many months out a certain task is from the launch date. The script will calculate the Start and End dates of each of these tasks, based on how many months out they are from the launch date.

See the original post for a great written walkthrough with visuals as well as a link to an example base.

Source Code

							
							let tasksTable = base.getTable('Tasks');
let tasksQuery = await tasksTable.selectRecordsAsync();
let taskRecords = tasksQuery.records
let releaseTable = base.getTable('Projects');

// Find out how many months out the Start Date is from the Launch Date
let monthsOutStart = [];
    taskRecords.forEach(function(c){
    // Get Launch Date
    let launchDate = new Date(c.getCellValue('Launch Date'));
    // Get the Month Value from the Single Select String
    let valueTest = parseInt(c.getCellValue('Months Out')[0].name.charAt(c.getCellValue('Months Out')[0].name.length - 2));
     // Test to Make sure it's a number. If it's not a number, then change to 0
    let value = isNaN(valueTest) ? 0 :  valueTest;
    // Find the integer type to understand if a date is pre or post launch
    let integerType = c.getCellValue('Months Out')[0].name.charAt(3);
    // If a date is pre launch, then make sure the addDate Value is negative.
    if(integerType == '-'){
    let addDate = (value * -1);
    let addMonth = launchDate.getMonth() + addDate;
    let endDate = new Date(launchDate.setMonth(addMonth));
    monthsOutStart.push(endDate);
    } 
    else{
    let endDate = new Date(launchDate.setMonth(launchDate.getMonth() + value))
    monthsOutStart.push(endDate);
    }
    });
let monthsOutEnd = []
    taskRecords.forEach(function(c,i){
    let launchDate = new Date(c.getCellValue('Launch Date'));
    let selectLength = c.getCellValue('Months Out').length - 1;
    let valueTest = parseInt(c.getCellValue('Months Out')[selectLength].name.charAt(c.getCellValue('Months Out')[selectLength].name.length - 2))
    let value = isNaN(valueTest) ? 0 : valueTest
    let integerType = c.getCellValue('Months Out')[0].name.charAt(3);
    if( integerType == '-'){
    let addDate = (value * -1)+1;
    let addMonth = launchDate.getMonth() + addDate
    let startDate = new Date(launchDate.setMonth(addMonth))
    monthsOutEnd.push(startDate);
    } 
    else{
    let startDate = new Date(launchDate.setMonth(launchDate.getMonth() + value+1));
    monthsOutEnd.push(startDate);
    }
    });

let newMonthsOutEnd = []
    monthsOutEnd.forEach(c => newMonthsOutEnd.push(c));
let newMonthsOutStart = []
    monthsOutStart.forEach(c => newMonthsOutStart.push(c));

let updateRecords = newMonthsOutStart.map((c,i) => (
    {id: taskRecords[i].id,
    fields: {
        'Start Date': c,
        'End Date': newMonthsOutEnd[i],
        }
    })
    );

await tasksTable.updateRecordsAsync(updateRecords)