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

[VictoriaPlummer]

Hey Y’all,

Sometimes I plan thing out way far is advance, like say 9 months out from a pretty important launch date - like my best friend’s wedding! (So excited for her :bride_with_veil:t5: , but I digress.) There are certain dates that I want to estimate, but as I get closer to the date 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. So, how do I do this?

First I want to structure my table correctly, so that I can actually calculate my fuzzy date. My friend’s wedding is on October 11th, so I’ll plan back from that. What I’ve done in the below example is create two tables Projects and Tasks. In the Projects table, I’ve added my friend’s wedding date as the launch date, and am looking up that date as the launch date for each of my tasks.

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. As you can see, some tasks can last for multiple months, (because who finds her dress on the first try, amirite?) 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. For the example, I’ve assumed each task takes a month. I’ll dial it in once I get the dates on the calendar.

Okay that’s it. What do y’all think? How would you make this more useful? Script below, example base here.

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)