Search

Recent Posts
Recent Comments
Archives
Categories
… on Youtube
… on Twitter
 Wallpaper of the Week 41/2017 https://t.co/pterqNjCZq #catina #vitamins #source #natural #medicine #health… https://t.co/zAoAp1zcvW3 days ago
 RT @VicolAudio: First Quantum SE ready for shipping to Europe. https://t.co/JqB7mmPujk17 days ago
 Wallpaper of the Week: 39/2017 #Palace of the #Parliament  Interior #Bucharest #Romania #cover #lights #parliament… https://t.co/2qZdkXYy6A18 days ago
 Wallpaper of the Week: 38/2017 #Zwinger: frames https://t.co/f7ju8LRfVK #Dresden #Germany #canon #cover #wallpaper https://t.co/0qnGbKMaIS24 days ago
Category Archives: CalculatedColumn
Calculated Column – get first Monday on the next month
If you have a column Data1 – DateTime type column , only date – and you need to get first Monday of the following month, the needed formula in a calculated column will be: =IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=1;DATE(YEAR(Data1);MONTH(Data1)+1;1+1);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=7;DATE(YEAR(Data1);MONTH(Data1)+1;1+2);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=6;DATE(YEAR(Data1);MONTH(Data1)+1;1+3);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=5;DATE(YEAR(Data1);MONTH(Data1)+1;1+4);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=4;DATE(YEAR(Data1);MONTH(Data1)+1;1+5);IF(WEEKDAY(DATE(YEAR(Data1);MONTH(Data1)+1;1))=3;DATE(YEAR(Data1);MONTH(Data1)+1;1+6);DATE(YEAR(Data1);MONTH(Data1)+1;1))))))) Depending on your regional settings, … Continue reading
Posted in CalculatedColumn, Sharepoint Online
Comments Off on Calculated Column – get first Monday on the next month
Sum of time values for two DateTime columns
Starting from this post let’s suppose that your two DateTime Columns are COL_DAT1 COL_DAT2 and the request is to sum the time values. The final value ( as a text ) will be in this format hh:mm. For this you will need … Continue reading
Posted in CalculatedColumn, Sharepoint Online
Comments Off on Sum of time values for two DateTime columns
Validation item in Sharepoint
…this post was published on 2017 March 03. How we can force users to fill a secondary line text if another column ( yes/no ) is checked as Yes Suppose that , the Yes/No column with the name yesno, and as single line text column … Continue reading
Posted in CalculatedColumn, Sharepoint, Sharepoint Online, Validation
Comments Off on Validation item in Sharepoint
Empty DateTime Field in Calculated Column
…this post was published on 2016 May 17. You need to check and maybe to use later if a Datetime field is empty or not? Even if it is empty, that doesn’t mean that no value is there. The value stored inside is:30.12.1899 … Continue reading
Posted in CalculatedColumn, Sharepoint
Comments Off on Empty DateTime Field in Calculated Column
Calculated Columns for Yes/No columns in Sharepoint
…this post was published on 2016 May 3. Starting from this post, let’s suppose that we have following colunms: Col_A, COL_B, COL_C, COL_D and COL_E. All columns are Yes/No Columns. The default value for them it isn’t so important ( in calculation columns, … Continue reading
Posted in CalculatedColumn, Sharepoint Online
Comments Off on Calculated Columns for Yes/No columns in Sharepoint
Calculating Working Hours – same day with lunch break
…this post was published on 2016 March 31. What if we need to calculate working hours in a day, minus lunch time? This request it’s also present on MS Forum. Let’s suppose that our list contain the following columns, DateTime type columns: … Continue reading
Posted in CalculatedColumn, Sharepoint Online
Comments Off on Calculating Working Hours – same day with lunch break
Calculating Working Hours – total working hours
…this post was published on 2016 March 31. Finally. last moment! TOTAL We need to add some columns. And because our last 2 columns are in minutes, we will transform first 3 columns from full days in minutes and will add the … Continue reading
Posted in CalculatedColumn, Sharepoint Online
Comments Off on Calculating Working Hours – total working hours
Calculating Working Hours – hours in last day
…this post was published on 2016 March 31. The final step is to calculate working hours in the last day. The procedure is similar with working hours in first day We need first, 2 new columns, calculated columns, which will calculate for … Continue reading
Posted in CalculatedColumn, Sharepoint Online, Uncategorized
Comments Off on Calculating Working Hours – hours in last day
Calculating Working Hours – hours in first day
…this post was published on 2016 March 31. The last 2 steps are those for calculating hours in first and last day. Let’s begin with the first day. As I said in main post, we assume that the working day begins at 8.30am … Continue reading
Posted in CalculatedColumn, Sharepoint Online, Uncategorized
Comments Off on Calculating Working Hours – hours in first day
Calculating Working Hours – days in full weeks
…this post was published on 2016 March 31. Well, this is the hardest step in this scenario because we need to analyze the interval between StartDate and EndDate, to get the number of full weeks. The structure could looks like below: To … Continue reading
Posted in CalculatedColumn, Sharepoint Online, Uncategorized
Comments Off on Calculating Working Hours – days in full weeks