I am working on a form which requires me to calculate difference between start and end time but it becomes an issue when end time is past midnight. Is there a way to do this calculation?
how do you calculate the difference?
dateDiff* functions should do the job correctly.
That would have worked if I had 2 dates but unfortunately I only have 1 date.
e.g. 24/03/2016 - Start 7pm End 3am
so, end is 25/03/2016 3am, am I right?
how do you enter both dates/times on the form?
Only start date is entered, end date is not entered.
Start Date Start Time End Time Total Hours
24/03/2016 07:00PM 03:00 AM ??????
what controls resp. what is datatype of start time and end time columns?
what control resp. what is datatype of start date?
are they just texts?
is there any specific reason not to use date picker controls?
I affraid not using date picker resp. not entering full datetimes for both timepoints you are going to make yourself many troubles.
how is end time related to start start time? is it always next day?
how do you treat the case when start time and end time are just the same? is it the same day or is end time next day?
how do you treat the case when start time is before afternoon (AM) and end time is past afternoon (PM)? is it the same day or two different days?
I find when dealing with these types of situations, its best to reduce the problem to the basic units. So if you're only interested in the hours difference, then reduce the two time elements to hours. So, 7pm to midnight + any end time less-than 7pm. With this you can simply use a Calculate Date action to do the rest. The same approach can be used if you need minutes instead of hours.
Retrieving data ...