Need Assistance - Calculation Between Two DATETIME

DelproServices · 07-14-2021 05:28 AM

Hey All,

I am in need of help trying to calculate the duration between two times. But I only need to get the after hours times to record for example.

Time will not calculate between:
08:00-18:00

But will between:
18:00-0800

Tech attends Job
Onsite Time, DATETIME [onsite_bh] = 14/07/2021 17:00
Offsite Time, DATETIME [offsite_bh] = 14/07/2021 20:00
Duration Shows: 2 Hours, which is correct. As one of those hours was done during business hours which is (08:00-18:00)

The issue is if the time was to show
Onsite Time, DATETIME [onsite_bh] = 14/07/2021 19:00
Offsite Time, DATETIME [offsite_bh] = 14/07/2021 20:00
Duration Shows: 2 Hours is shown which is incorrect it should only be 1hour.

Though if the time goes over to the next day which it can it will start to calculate it correctly for example

Tech Attendance
Onsite Time, DATETIME [onsite_bh] = 14/07/2021 19:00
Offsite Time, DATETIME [offsite_bh] = 15/07/2021 01:00
Duration Shows: 6 hours is shown which is correct.

Tech Attendance
Onsite Time, DATETIME [onsite_bh] = 14/07/2021 19:00
Offsite Time, DATETIME [offsite_bh] = 16/07/2021 01:00
Duration Shows: 37 hours is shown which is incorrect.

I have used this post as my guide as I have been looking for a solution but i can not resolve this my self and would like some help please. My expression is Below.

https://community.appsheet.com/t/overtime-work-based-on-duration-after-05-pm-and-before-am/36983/7

IF(
	DATE([offsite_bh]) = DATE([onsite_bh]),
	IFS(
		AND(
			TIME([onsite_bh]) >= TIME("00:00:00"),
			TIME([onsite_bh]) <= TIME("08:00:00")
		),
		IFS(
			TIME([offsite_bh]) <= TIME("08:00:00"),
			TOTALHOURS([offsite_bh] - [onsite_bh])*1.00,
			AND(
				TIME([offsite_bh]) > TIME("08:00:00"),
				TIME([offsite_bh]) <= TIME("18:00:00")
			),TOTALHOURS(DATETIME(DATE([offsite_bh])&" "&TIME("08:00:00")) - [onsite_bh])*1.00,
			TRUE,(TOTALHOURS(DATETIME(DATE([offsite_bh])&" "&TIME("08:00:00")) - [onsite_bh]) + TOTALHOURS([offsite_bh] - DATETIME(DATE([offsite_bh])&" "&TIME("18:00:00"))))*1.00
		),
		TIME([onsite_bh]) >= TIME("08:00:00"),
		IF(
			TIME([offsite_bh]) < TIME("18:00:00"),
			0,
			TOTALHOURS([offsite_bh] - DATETIME(DATE([offsite_bh])&" "&TIME("18:00:00")))*1.00
		),
		AND(
			TIME([onsite_bh]) >= TIME("18:00:00"),
			TIME([offsite_bh]) <= TIME("23:59:59")
		),TOTALHOURS([offsite_bh] - [onsite_bh])*1.00
	),
	TOTALHOURS(
		IF(
			TIME([offsite_bh]) >= TIME("08:00:00"),
			DATETIME(DATE([offsite_bh])&" "&TIME("08:00:00")),
			[offsite_bh]
		) - 
		IF(
			TIME([onsite_bh]) >= TIME("18:00:00"),
			[onsite_bh],
			DATETIME(DATE([onsite_bh])&" "&TIME("18:00:00"))
		)
	)*1.00
)

frankikoch

IFS((HOUR([DATE]-TODAY()))>=0, CONCATENATE ((HOUR([DATE]-TODAY())/), " TERM END TIME"))

I habe a column called [DATE] a write this expression in a virtual columm called [HOW MANY HOURS?].

DelproServices

Hey Frankikoch,

Thanks for the reply, I think you are asking to create a virtual column with that said expression to calculate the duration between the said given [DATE] and TODAY () though I need the user to be able to put in two different inputs and calculate any duration between two inputs [DATETIME ARRIVED] & [DATETIME LEFT] for example.

frankikoch

decimal((HOUR([DATETIME LEFT]-[DATETIME ARRIVED])))

DelproServices

Hey,

Yeah i understand that side of things, but i need it to be more complex and only calculate the time between 18:00:00 - 00:00:00 & 00:00:00 - 08:00:00. My post above explains. I only want times that are outside of work hours recorded for billing purposes.