Skip to main content
Participant
May 15, 2023
Question

Calculating quarters between dates with JavaScript

  • May 15, 2023
  • 1 reply
  • 2504 views

Hello,

 

I have two boxes that capture input for time and date. I need to calculate the number of quarters between the input but I'm new to JS so I'm not sure where to start.

 

I have the formula in Excel with A4 being estarrival and B4 being estdeparture

=ROUNDUP((B4-0.0416666666666667-A4)*4,0)

 

How can I make this in Javascript?

 

So far, I only have: 

 

var f = this.getField("estdeparture").value;
event.value = f;

 

I'm thinking something like this but it's not giving me a value obviously because "this.getField("estdeparture").value;" is giving me the literal string when i try some basic debugging.

 

var depart = this.getField("estdeparture").value;
var return = this.getField("estreturn").value;

var final =  return - 0.0416666666666667 - depart * 4;
event.value = final;

 

Any help is appreciated!

This topic has been closed for replies.

1 reply

maxwithdax
Community Expert
Community Expert
May 16, 2023

The script you provided seems to have a couple of issues. Here are the problems and their respective solutions:

  1. Invalid variable name: You are using a reserved keyword, "return," as a variable name. This will cause a syntax error. Change the variable name to something else, such as "returnDate."

  2. Incorrect calculation: The formula for calculating the number of quarters between two dates is incorrect. Subtracting a constant value (0.0416666666666667) and multiplying the difference by 4 will not yield the desired result. Instead, you need to calculate the difference between the two dates and divide it by the number of milliseconds in a quarter.

Try this:

 

var depart = this.getField("estdeparture").value;

var returnDate = this.getField("estreturn").value;

var startDate = new Date(depart); var endDate = new Date(returnDate);

var differenceInMilliseconds = endDate - startDate;

var quarters = differenceInMilliseconds / (1000 * 60 * 60 * 24 * 30 * 3); // Assuming each quarter has 3 months

event.value = quarters;

 

The script calculates the difference in milliseconds between the two dates and divides it by the number of milliseconds in a quarter. The result will be the number of quarters between the dates, which will be assigned to event.value.

 

Participant
May 16, 2023

Thanks maxwithdax for this reply. I think there was a misunderstanding on the intent of this script. I'm calculating the quarters between two dates and time. There are four quarters to a day. I think your script calculates how many quarters per month.

 

The excel formula i provided works but I'm trying to translate that into JS. In Excel, a date like "6/9/2023 2:00 PM" can be represented by 45086.58 in Excel. I'm not sure what this number is or what excel did to make it represent that number. I think if I can get JS to convert that time and date to the same number, I can figure out the rest of it. 

 

maxwithdax
Community Expert
Community Expert
May 19, 2023

Ah..days... okay. yeah