Auto calculating shift end date

bfuchs
bfuchs used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Top Expert 2016

Commented:
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.
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
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

leakim971Multitechnician
Top Expert 2014

Commented:
you may(should?) use a Caspio trigger on insert/update
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
See attached what I mean by creating a calculated field in Caspio.

Thanks,
Ben
Capture.PNG
leakim971Multitechnician
Top Expert 2014

Commented:
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 );
could you confrm this is what you're looking for ?
How do I test this?
leakim971Multitechnician
Top Expert 2014

Commented:
just confirm the logic first, the function converting is wrong currently
Actually no, the logic needs to be compared both shifts, the Shift_From_Hour to the Shift_To_Hour.
The goal here is to find out if Shift_To_Hour is the same day as Shift_From_Hour or not.
leakim971Multitechnician
Top Expert 2014

Commented:
compared? could you describe with example?
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.
leakim971Multitechnician
Top Expert 2014

Commented:
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
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.
Shift_Start_Date) + Shift_Hour_To_AMPM
How can you use this with a text field?
leakim971Multitechnician
Top Expert 2014

Commented:
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

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

Commented:
you point out a profile (odba, not an abswer)
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?
leakim971Multitechnician
Top Expert 2014

Commented:
// 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
Multitechnician
Top Expert 2014
Commented:
function mil(str) {
    var t = str.split(':')
    var hh = parseInt(t[0],10);
    var isAM = str.toLowerCase().indexOf('am')!=-1;
    hh -= (isAM && hh==12)?12:0;
    hh += (isAM)?0:12;
    if (hh<10) hh="0"+hh;  
    return ""+hh+':'+t[1];
}

var Shift_From_Hour = $("select[id*=Shift_From_Hour]").val();
var Shift_From_AMPM = $("select[id*=Shift_From_AMPM]").val();
var Shift_To_Hour = $("select[id*=Shift_To_Hour]").val();
var Shift_To_AMPM = $("select[id*=Shift_To_AMPM]").val();

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

if(isSameDay) {
   // do something if same day
} else {
   // do something else
}

Open in new window

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?
leakim971Multitechnician
Top Expert 2014

Commented:
if from<to so that make sense we same day
Thank you!
leakim971Multitechnician
Top Expert 2014

Commented:
you welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial