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

javascriptgoogle-sheetsgoogle-calendar-api

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.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Searching for values in nested arrays using lodash.find

Presented below is an array where I aim to employ lodash for item search: [ { "itemA": "apple", "itemB": [ { "itemC": "1", "itemD": "red apple" }, { "itemC": "2", "itemD": "green apple" }, ...

javascriptarraysnode.jslodash

Access the Express server by connecting to its IP address

Is it feasible to connect to an express server using the server's UP address? If so, how would one go about doing this? (Examples of code snippets would be greatly appreciated) ...

javascriptnode.jsexpressserverip

I encountered a challenge with a cross-site scripting filter that is preventing me from storing a JavaScript variable in a MySQL database using a

I am currently working on a project where I need to save a JavaScript variable into a MySQL database using a Python CGI script. In this case, the JavaScript variable contains the following information: <li style = "width: 300px; background-color: white ...

javascriptpythonwebkitcgixss

Stop jQuery Tab from Initiating Transition Effect on Current Tab

Currently, I am utilizing jQuery tabs that have a slide effect whenever you click on them. My query is: How can one prevent the slide effect from occurring on the active tab if it is clicked again? Below is the snippet of my jQUery code: $(document).read ...

javascriptjqueryhtml

Using sinon.js version 1.10, jQuery version 2.1, and making synchronous requests

I have been attempting to simulate a server using sinon.js and calling it with jQuery.ajax, but I am facing issues getting it to work. Here is the code snippet: $(function() { var server = sinon.fakeServer.create(); server.respondWith('POST&apo ...

javascriptjqueryajaxsinon

Validate forms using jQuery with the power of ajax

Is there a way to effectively check for the existence of a username? I want to increment a variable called "form_error" if the username already exists. If "form_errors" is greater than 0, I need the code to stop and return false. However, when I use an Aj ...

javascriptjqueryajaxvalidation

Understanding the Vue lifecycle methods for updating Vuex state

Utilizing Vue and Vuex components, the code within my component consists of: computed: { ...mapState({ address: state => state.wallet.address }) }, The functionality operates smoothly in the user interface. However, my objective is to invoke a ...

javascriptvue.jsvuex

Determine the total value derived from adding two option values together

I am attempting to calculate the sum of two select options and display the result. So far, my attempts have only resulted in returning the value 1. What I am aiming for is to add the values from n_adult and n_children, and then show the total under Numbe ...

javascriptjqueryajax

Magento - when the page just can't take it anymore

I've encountered a problem with my Magento store. Half of the page is displayed and then it breaks. Here's the link to the page: When I check the page source, this is the code where it seems to break: <script type="text/javascript> ...

phpjavascriptmagento

What is the best way to manage horizontal scrolling using buttons?

I was hoping that when the button is clicked, the scroll would move in the direction of the click while holding down the button. Initially, it worked flawlessly, but suddenly it stopped functioning. export default function initCarousel() { const carous ...

javascripthtmlcss

What is the main object used in a module in Node.js for global execution?

Node.js operates on the concept of local scope for each module, meaning variables defined within a module do not automatically become global unless explicitly exported. One question that arises is where a variable declared in a module file belongs in term ...

javascriptnode.jsscope

Analyzing latency in node.js through numerous requests

Is there a way to send multiple requests in a loop, and measure the time of each request? In PHP I'd do: require 'vendor/autoload.php'; use GuzzleHttp\Client; $client = new Client(); for ($i = 0; $i < 100; $i++) { $start = mic ...

javascriptnode.jsrequest

Combining a 3D array into a 2D array with the addition of HTML tags around each value using JavaScript

3D array // Array var x = { "letter": [ "a", "b", "c", "d", "e", "f", "g", "h", "i" ], "line": [ { "data": [ 306, 830, 377, 651, 547, 369, 300, 148, 494 ] }, { "data": [ 88, 339, 298, 87, 96, 108, 93, 182, 64 ] }, { "data": [ 3157, 2943, ...

javascriptarraysmultidimensional-array

Data on global map routes displayed in highcharts react

After reviewing the react highmaps documentation, it appears that they have saved the map data in a hardcoded manner. Take a look at Link 1 Check out Link 2 In various tutorials, I noticed that the data is retrieved from high maps itself by passing a ke ...

javascriptnode.jsreactjshighchartsnpm

Looking to enclose a search query in JavaScript using quotes, and then remove the quotes on the Python backend

Let's start with my code snippet. This is the HTML section: <form action= "/" onSubmit= "return validate(this);" method= "post"> <!--irrelevant from this point--> Below is the Javascript portion, located later in the file: <scri ...

javascriptpythonflaskurllibencodeuricomponent

Creating a JSON File with an Illustrator Script

Currently, I've been working diligently on developing a system that allows me to export my swatches from Illustrator as a JSON object. This will greatly simplify the process of updating my App. By utilizing the illustrator scripting API, I've suc ...

javascriptjson

Updating a property on an object during iteration

Currently, I am in the process of developing a Single Page Application using Laravel on the backend and Vue.js. I have two arrays that are crucial to my project: The first array is accessArray: ["BIU","CEO","Finance","HRD","Group"] The second array is a ...

javascriptjqueryvue.js

Understanding the response from an AJAX call

VB code Dim temp3 As String = dt.ToString() cmd.Connection = con con.Open() i = cmd.ExecuteNonQuery() con.Close() If i = 1 Then msg = "Record successfully inserted" ...

javascriptc#jqueryasp.netajax

Using jQuery Ajax to send data and retrieve responses in the Codeigniter framework

I am struggling with passing values in CodeIgniter and I need some guidance. Could you provide an example code snippet using CodeIgniter to send a value from a view to a controller using Ajax and jQuery, and then display the result on the same page? In my ...

javascriptphpjqueryajaxcodeigniter

Gatsby MDX fails to locate the desired page despite displaying the title and slug

Currently diving into the world of Gatsby and I've decided to implement MDX for my blog pages. Following a helpful tutorial here on programmatically creating pages. All seems well as I can view them in my GraphQL and displaying the list of articles i ...

javascriptreactjsgraphqlgatsby