Start Free Trial

asked on

Auto calculating shift end date

Hi Experts,

I have a scheduling table with the following fields
Visit_Date (Date/Time)
Shift_From_Hour (Number)
Shift_From_Min (Number)
Shift_To_Hour (Number)
Shift_To_Min (Number)
See first attachment how they look like.

Now was requested to add 4 additional fields
Shift_From_AMPM (Text)
Shift_To_AMPM (Text)
Shift_Start_Date (Date)
Shift_End_Date (Date).
See second attachment, how they will look like.

My Question is, how can I program that Shift_End_Date should be auto filled upon calculating both shifts, either with same day as Shift_Start_Date or next day?

Thanks
Capture1.PNG
Capture.PNG

ASKER

Attaching sample data structure.

Also since this is going to be used in a Caspio datapage, perhaps the following link can give a better explanation on what we are looking for.

https://howto.caspio.com/tech-tips-and-articles/common-customizations/separate-input-fields-for-datetime-parts/

Thanks
Skilled_Nursing_Visit_Note_2_2019-Se.zip

David Johnson, CD

Medical_Record_Number date/time ?
I would leave the database Shift_From to Shift_To as Date/Times in the database
You can change how it is presented to the user split apart.

ASKER

Hi,

Medical_Record_Number date/time ?

That field is not relevant here.

I would leave the database Shift_From to Shift_To as Date/Times in the database

They're currently number fields and I cannot change it.
Thanks,
Ben

you may(should?) use a Caspio trigger on insert/update

ASKER

Hi,

you may(should?) use a Caspio trigger on insert/update

It doesn't actually need to be a field, it could be a calculated field and I would rather prefer that, and that can be done using SQL.
Thanks,
Ben

ASKER

See attached what I mean by creating a calculated field in Caspio.

Thanks,
Ben
Capture.PNG

could you confrm this is what you're looking for ?

var intDate = Convert_In_Second_19700101(Shift_Start_Date) + Shift_Hour_From_AMPM * 3600 + Shift_Min_From_AMPM * 60;
Shift_End_Date = Convert_To_Date( intDate );

ASKER

could you confrm this is what you're looking for ?

How do I test this?

just confirm the logic first, the function converting is wrong currently

ASKER

Actually no, the logic needs to be compared both shifts, the Shift_From_Hour to the Shift_To_Hour.

ASKER

The goal here is to find out if Shift_To_Hour is the same day as Shift_From_Hour or not.

compared? could you describe with example?

ASKER

If Shift_From_Hour is 11, Shift_From_AMPM is PM and Shift_To_Hour is 1, Shift_To_AMPM is AM then we know they are not at the same day.

OK so something like that (don't try it, just for the logic) :

var fromDate = Convert_In_Second_19700101(Shift_Start_Date) + Shift_Hour_From_AMPM * 3600 + Shift_Min_From_AMPM * 60;
var Shift_From_Date = Convert_To_Date( fromDate );

var toDate = Convert_In_Second_19700101(Shift_Start_Date) + Shift_Hour_To_AMPM * 3600 + Shift_Min_To_AMPM * 60;
var Shift_To_Date = Convert_To_Date( toDate );

var isSameDay = getDayFromDate( fromDate) == getDayFromDate( toDate ); // true or false

ASKER

While If Shift_From_Hour is 11, Shift_From_AMPM is AM and Shift_To_Hour is 11, Shift_To_AMPM is PM then they are at the same day.

ASKER

Shift_Start_Date) + Shift_Hour_To_AMPM

How can you use this with a text field?

check this : https://www.experts-exchange.com/questions/26727597/JavaScript-Convert-time-format-to-24-Hour-with-leading-zeros.html
So using the last answer code :

var mil1 = mil(Shift_From_Hour + Shift_From_AMPM);
var mil2 = mil(Shift_To_Hour + Shift_To_AMPM);
var arr1 = mil1.split(":");
var arr2 = mil1.split(":");
var isSameDay = mil1[0]*3600 + mil1[1]*60  <  mil2[0]*3600 + mil2[1]*60; // true of false

Open in new window

ASKER

I was about to suggest we follow this logic, but whatever works is fine.

you point out a profile (odba, not an abswer)

ASKER

ASKER

What will be the results of

var mil1 = mil(Shift_From_Hour + Shift_From_AMPM);

Open in new window

When Shift_From_Hour = 11 and Shift_From_AMPM = PM?

// When Shift_From_Hour = 11 and Shift_From_AMPM = PM?
// we've : 11 + "PM", mean 11PM
var mil1 = mil("11PM"); // mil return 23:00"

// When Shift_To_Hour = 11 and Shift_To_AMPM = AM?
var mil2 = mil("11AM"); // mil return "11:00"

var arr1 = mil1.split(":"); // arr1 = [ "23", "00" ]
var arr2 = mil1.split(":"); // arr2 = [ "11", "00" ]
var isSameDay = 23*3600 + 0*60 < 11*3600 + 0*60; // false so it's the next day

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

var isSameDay = 23*3600 + 0*60 < 11*3600 + 0*60; // false so it's the next day

not sure I'm following the concept, but by looking at my Calculator
23*3600 + 0*60 = 4968000
11*3600 + 0*60 = 2376000
looks like makes sense...
What are the meaning of those numbers?

if from<to so that make sense we same day

ASKER

Thank you!

you welcome!