rounding time after 23:00 problem

[Daniel_Bennett]

clocking in system rounding time up to the nearest 15mins, works until 23:00 and after 00:00. This 1 hour segment is the only issue all other times work well. also have the same issue rounding down. here is my failed attempt at resolving the issue 

any help greatly appreciated 

IF(HOUR(TIME([clock in]) - "00:00:00")<23,

CONCATENATE(DATE([clock in])," ",TIME(
TIME(HOUR([clock in]-"0:00")+"1:00")-
IFS(
MINUTE([clock in]-"0:00")>=45,"0:00",
MINUTE([clock in]-"0:00")>=30,"0:15",
MINUTE([clock in]-"0:00")>=15,"0:30",
MINUTE([clock in]-"0:00")>=0,"0:45",

)
)
),
CONCATENATE(DATE([clock in])," ",TIME(
TIME(HOUR([clock in]-"0:00")+"0:00")-
IFS(
MINUTE([clock in]-"0:00")>=45,"0:00",
MINUTE([clock in]-"0:00")>=30,-"0:15",
MINUTE([clock in]-"0:00")>=15,-"0:30",
MINUTE([clock in]-"0:00")>=0,-"0:45",

)
)
)



)

[Suvrutt_Gurjar · Accepted solution]

Please take a look at the following Tips posts

FAQ: Rounding DateTime, Duration, and Time values - Google Cloud Community

Set Initial Value of TIME column to nearest 15 min... - Google Cloud Community