Automatically increase the dates in two cells once they have passed

Question

Automatically increase the dates in two cells once they have passed

Summary:

Although I'm not a programmer, I've managed to incorporate some complex coding into a Google Sheets document for tracking my team's projects. This includes multiple-variable dropdown menus and integration with Google Calendar to monitor project development.

Why I'm Seeking Help:

While I usually piece together code fragments from internet forums successfully, this time I'm struggling to find the information I need to proceed.

What I am Looking For:

I have 5 cells that are structured as follows:

Date start - Date end - date code* - number** - Priority***

*I need a script to add the date range to Google Calendar.

** & *** The "number" cell should display values based on the value in the "Priority" cell. For example, if "Weekly" is selected as the priority, the "number" column will show "7" in the corresponding cell (monthly = 30, etc.).

I'm looking for assistance in creating a script that functions as follows:

If I set the priority to weekly, it will display "7" in the "number" column. Then, every time the "Date end" has passed, it will automatically add 7 days to both the "Date start" and "Date end."

This setup will allow me to continuously track our projects effectively.

Thank you in advance for any guidance provided.

PS: While I've come across similar discussions involving SQL, I lack the expertise to implement the suggested solutions.

Edit: View Spreadsheet Image eDIT2: Spreadsheet with an increment column

javascript google-sheets google-calendar-api

Answer 1

Answer №1

Regarding the data set and description, VBA may not be necessary for achieving increment by adding +1 to the reference pointing to the previous cell. For instance, if cell A1 is formatted as a Date, you can input in cell B1: =A1+1, then in cell C1: =B1+1, and so forth. The outcome should display as follows:

A           B           C      
9/1/2017    9/2/2017    9/3/2017

This process could be enhanced with a simple condition to only show the incremented value if the preceding cell is not empty, such as: =IF(A1,A1+1,"")

In your scenario, it might involve cell F1 containing =IF(E1,E1+1,"").

It's worth noting that the underlying value of Date is essentially an Integer value (with Time represented as the decimal part), allowing arithmetic operations to be performed.

A more versatile solution could utilize the Excel DATE() Worksheet formula, like demonstrated in this example (adding 1 month to the date entered in cell A1):

=DATE(YEAR(A1), MONTH(A1)+1, DAY(A1))

To incorporate additional logic, consider utilizing the Excel Worksheet IF() statement, as shown in this sample scenario where cell B1 contains:

=A1+IF(C1="week",7,1)

A           B           C
9/1/2017    9/8/2017    week

Based on the IF() condition, it will add either 7 days if C1 contains the word "week," or 1 day otherwise. Further complexity can be achieved with nested IF() statements.

I trust this information proves helpful.

Answer 2

Regarding the data set and description, VBA may not be necessary for achieving increment by adding +1 to the reference pointing to the previous cell. For instance, if cell A1 is formatted as a Date, you can input in cell B1: =A1+1, then in cell C1: =B1+1, and so forth. The outcome should display as follows:

A           B           C      
9/1/2017    9/2/2017    9/3/2017

This process could be enhanced with a simple condition to only show the incremented value if the preceding cell is not empty, such as: =IF(A1,A1+1,"")

In your scenario, it might involve cell F1 containing =IF(E1,E1+1,"").

It's worth noting that the underlying value of Date is essentially an Integer value (with Time represented as the decimal part), allowing arithmetic operations to be performed.

A more versatile solution could utilize the Excel DATE() Worksheet formula, like demonstrated in this example (adding 1 month to the date entered in cell A1):

=DATE(YEAR(A1), MONTH(A1)+1, DAY(A1))

To incorporate additional logic, consider utilizing the Excel Worksheet IF() statement, as shown in this sample scenario where cell B1 contains:

=A1+IF(C1="week",7,1)

A           B           C
9/1/2017    9/8/2017    week

Based on the IF() condition, it will add either 7 days if C1 contains the word "week," or 1 day otherwise. Further complexity can be achieved with nested IF() statements.

I trust this information proves helpful.

Automatically increase the dates in two cells once they have passed

Answer №1

Similar questions

jQuery element with listener not triggering as expected

Manipulating datetime format within an input element of type date using Angular's ngModel

Is JavaScript Promise Chaining Allowed?

Angular is throwing a RangeError due to exceeding the maximum call stack size

Combining arrays in Javascript that share the same key by utilizing a loop

What is the functionality of the toArray method?

Error encountered in Node/Express application: EJS partials used with Angular, causing Uncaught ReferenceError: angular is not defined

Choosing a particular 2D array based on another variable in jQuery and JavaScript

How can I enhance data from an AJAX callback in Mapbox with an additional layer?

Guide on darkening the surrounding div of an alert to give it a modal-like effect

adjust time in jQuery AJAX request when a button is clicked

Having trouble with validation messages not displaying correctly in Angular after removing a value. What is the correct approach to fix this issue

Design an element that stretches across two navigation bars

Why do I keep encountering the error of an undefined variable? Where in my code am I making a mistake

Tips on utilizing CSS modules in React without changing class names

Tips for testing a service in Angular using unit testing techniques

Extracting information from within Ajax's Jsonp

Adjusting the Connection header in a jQuery ajax request

What is the best way to target all elements sharing a common class?

Obtain every Scope within an AngularJS application