SharePoint calculated column formula in List not consistent
I have a custom column that uses this formula to create a unique ID: =CONCATENATE(Section,REPT(0,4-LEN(ID)),ID) It works fine most of the time, then it seemingly drops the ID value. One thing to mention this ID value is the system generated value found in the ID column of any SharePoint List, not a user created column. So for example the first item has a unique identifier of ABC0001, the tenth has ABC0010 and so on. That worked fine and then i noticed that the unique number for what once was ABC0005 is now ABC0000. If i go to the calculated column and update the parameters in the formula it will update and resume showing the correct value, ABC0005 for this example. Since ID is a system generated value and this formula is embedded in a column what could cause it not evaluate the formula to true and thus the unique ID randomly?
Help with Text to Date Formula
Hello, I have been using many different methods for attempting to convert text and date - but failing miserably! Essentially, the way I can download data provides dates that appear like this: Jun 13th 2017 10:44 Oct 03rd 2017 16:18 Jul 03rd 2017 16:10 Dec 21st 2016 15:22 Jul 05th 2017 14:07 Does anyone know a formula and steps involved that I can please use to convert this to appear in dd/mm/yyyy format please? I can get rid of the time at the end, but every approach I've tried so far just throws up #VALUE error. I need to do this to show how much time has elapsed since each day until (TODAY). Any advice very welcome - thank you!Removing full stops from a number sequence
Hi all, We have a single line text column that we use to ID folders. The ID's follow this structure 1.1.2.1.2 Is there a way a calculated field can take this sequence and remove the full stops between the numbers so instead of 1.1.2.1.2 we get a calculated column that returns 11212? Thanks!
Fields with Formulas
Hi gang, I am using an accounting software called Xero, that has the ability to utilise custom templates I create in Word. My restaurant chain has a Central Kitchen and a Bakery. Both invoices are added into Xero but need to be split up when I am sending the Statement (payments are made into separation bank accounts). The Statement feature currently consolidates all invoices (from both the Bakery and CK). I need to find a way of splitting up the lines so that only the CK appears in the table; and visa versa for the Bakery. A difference between the Invoices is the Invoice Number (field code: <<Description>>). All Central Kitchen Invoices start with a "CK-"; and the Bakery with "BK-". Is there a field code formula to only capture data that contains the above in its reference, including all of the subsequent data on that Line? Perhaps you have another suggestion for this. I have attached a document for you reference. I have 2 separate tables where I would like the data split. Any help is greatly appreciated! Thank you very much, DannyColumn Default Value Based on Root URL
I have a tab in an MS Teams team where users can view our upcoming corporate training sessions (i.e. Events). In each event description, I am putting a link to a sign up form. The sign up form is a list in MS SharePoint Online. In this list, there is a column to specify the event to which the user wants to sign up. I want this column to be automatically filled using the column default value function in SharePoint. The default value would be based on the root URL that led the user to the sign up form. For example, if the user was viewing training session X in Teams, it means the root URL is the event X URL. So, the formula would be: IF [Root URL] = (URL of event X) then "X", ELSEIF [Root URL] = (URL of event Y) then "Y"... and so on. Does this hypothetical [Root URL] function even exist? If not do you have any suggestion for how I can tell which event the user is coming from such that I can fill the column automatically? Thanks.
Pivot Table StDev calculates different value then the STDEV formula
Hi, I'm using Excel 2013 15.0.5153.1000 32-Bit and a pivot table to calculate StDev and compare quickly to the StDev calculated by the excel formulas. I noticed that sometimes the Pivot Table StDev function gives a different value compared to the StDev calculated by the excel formula. When i found this i compared the StDev of the pivot table with every existing stdev formula from excel to see if i can get a match with one of the 6 formulas. In the attached file you can see this comparison with both StdDev and StdDevp function of the pivot table. 12 times out of 50 the StDev doesn't match. The error is very small, only the last few decimals are different. Column J contains the values StdDev and StdDevp calculated by the pivot table, in columns K:P you can see the values calculated by the excel formulas. On rows 8, 10, 15, 16, 24 and 26 you can see that the StDev value from the pivot table doesn't match with any value calculated by the excel formulas. (Same thing for the StdDevp only the row numbers are different). I'm curious if anybody knows the reason for this. Did anybody met this issue before? I can't find anything on the internet regarding this.
SharePoint 2013 LIst Filters (by latest date)
I am trying to sort a list by the latest date before a predefined date. I need it to also sort on 2 other columns. Basically I need the last date entered before [Date] for each drop down choice Drop down Column 2 Date End Date - (latest record after a date) I found a few things online but I'm not sure where I would add this or what the best plan of attack would be. Filter(ListName,Date=First(Sort(Distinct(ListName,Date),Result,Descending)).Result) Any help or direction is greatly appreciated.Leap year formula
Over the years I've run into several situations where we've needed to determine if the year is a leap year; most recently to determine the number of working days to calculate billable time - not the important part. I remember being taught as a kid the easy way to figure it out in your head but couldn't find a simple way to do in an Excel formula/cell. Figure out in your head: I'm realizing a lot of the tricks I was shown as a kid may not be making it into the school system or are just lost arts of thinking...so I will share and hopefully you will learn something new as well. Looking at positions 3 and 4 of the year is all you need to determine the leap year. Is position 3 odd or even? (in 2019 this is the "1") Odd: then only the last position of "2" or "6" are a leap year. So 2012 and 2016 were leap years. Even: then the last positions of "0", "4" or "8" are leap years. So 2002, 2004, 2008 were all leap years. Feel free to check me on any year....1944 - yep a leap year. 1972 - also a leap year. Knock yourself out. Excel formula So even with this knowledge it is not easy to turn this into usable information in Excel...until this formula. It is applying the same logic above, into a single field formula. It can be a little confusing so let's break it down. Here is the pseudo syntax to help it all make sense: If the 3 position of the year is evenly divisible by 2 then A (even), else B (odd). A (even): If the 4th/last position of the year is evenly divisible by 4 then it is a leap year, if not it isn't a leap year. B (odd): If the last position of the year is either a 2 or a 6 then it is a leap year, if not it isn't a leap year. Now that wasn't too hard...but it can be difficult and get lost in the formula. So breaking down the formula to align with this pseudo code can help. We have to explain some of the values in our formula - so let's start with the assumption that our date is in cell A3 (I also have the $ reference in front of A as this is the column for all of my dates, but I'm going to copy this to multiple rows). Let's also assume that we want to return the value of "Leap" for a leap year and "No" for any other year...we can then have other cells look for this reference without duplicating all of this logic through out your spreadsheet - one cell in each row for my situation. If the 3 position of the year is evenly divisible by 2 then A (even), else B (odd). =IF(MOD(MID(YEAR($A3),3,1),2)=0, {EVEN},{ODD}) Now the sub if statements A (even): If the 4th/last position of the year is evenly divisible by 4 then it is a leap year, if not it isn't a leap year. IF(MOD(MID(YEAR($A3),4,1),4)=0,"Leap","No" B (odd): If the last position of the year is either a 2 or a 6 then it is a leap year, if not it isn't a leap year. IF(OR(MID(YEAR($A3),4,1)="2",MID(YEAR($A3),4,1)="6"),"Leap","No" When you put it all together this is what it looks like. =IF(MOD(MID(YEAR($A3),3,1),2)=0,IF(MOD(MID(YEAR($A3),4,1),4)=0,"Leap","No"),IF(OR(MID(YEAR($A3),4,1)="2",MID(YEAR($A3),4,1)="6"),"Leap","No")) I hope this helps or gives you ideas for other problems you can solve.
