Hi
I want to update the Start and End time column based on condition :-
Start -
1) If we have minutes less then 30 minutes then we will reduce it to round of 00 minutes like
like - 16:15 will become 16:00 or 16:29 will become 16:00
2) If we have minutes more then 30 minutes then we will decrease it to 30 minutes.
like - 16:45 will become 16:30 or 16:53 will become 16:30
End -
1) If we have minutes less then 30 minutes then we will increase it to round of 30 minutes like
like - 16:15 to 16:30 or 16:29 to 16:30
2) If we have minutes more then 30 minutes then we will increase it to 00 minutes.
Like - 16:45 to 17:00 or 16:59 to 17:00
I hope I have given some clarity let me know if further clarification required please help little bit tricky as the time metedata us also string.
Thank you so much :)
Solved! Go to Solution.
@gautiergodard no that is a different scenario.
@sgaryali how do you get on with something like this? Basically just extracting the minutes, converting them to a number and applying the necessary checks, before then rounding them to the relevant value and - in the case of [End] - also increasing the hour by one if it needs rounding up:
Good job @DataNath
You just missed the edge case where end is >=23:31. Change your formula to this
IIF(!EndsWith([End], '00'),
IIF(ToNumber(Right([End], 2))<30,Left([End], 3)+'30',
IIF(ToNumber(Left([End],2))<23,ToString(ToNumber(Left([End], 2))+1)+':00','00:00')),[End])
To give a valid time of 00:00
Dan
Ah of course @danilang - totally slipped my mind. Great spot thanks!