Thursday, 10 April 2014

Calculated Column DateTime to date Convert

I was recently working with Calculated Column and i face a horrible nightmare type issue. I just need to copy date-time column value to a date type column. But it takes at least 3 to 4 hours of mine. Then I found this. I forget to get link but I get this much info which i want to share with you.

 =IF(TEXT(([End Time]-[Start Time])-TRUNC(([End Time]-[Start Time]),0),"0.000000000")="0.999305556",[Start Time]+1,[Start Time])
Basically, the formula checks whether the event is an all day event and if so increases the value by whatever you need. It determines whether the event is all day by checking the difference between the end time and smart time (12:00am to 11:59pm).
Here's the formula as I used it:
=IF(TEXT(([End Time]-[Start Time])-TRUNC(([End Time]-[Start Time]),0),"0.000000000")="0.999305556",[Start Time]+1,[Start Time])
So, get the difference between end time and start time, then subtract the truncated value of the same value (get rid of the value to the left of the decimal which would indicate that the all day event spans multiple days). If the difference is .999305556 then I'm adding 1 day to the value of start time (since I'm only interested in showing the date, not the time), otherwise leave the date/time as is.
Hope that helps someone else with calculated date columns.


No comments:

Post a comment