Fearless Date and Time Formulas

welcome thank you for joining me this afternoon for fearless state of time formulas my name is Beth brightness I'm a developer at Salesforce org and I'm this is like the fifth or sixth time I've done some version of this presentation about formulas I'm super passionate about empowering admins to really think programmatically just in formulas so thank you all for joining me to level up your formula game I will be talking about some road mapping things when we get to the time section so please take note of the forward-looking statement and only make purchasing decisions based on things that are currently generally available all right who has at least one custom date field in your org yes that's what I expect pretty much every hand in the room we all care about dates we have birthdays and memberships and expiry dates and contract dates and programs and anniversaries and it goes on and on I'm sure you could all name ten more examples of a custom date field use case and now we also have time so who cares about time do any of you have yet have a time field in your org oh wow I want to talk to all of you about your use cases after this session this is super new we'll get into sort of the journey that time fields have been on in a little bit but time also super relevant to businesses and organizations right we've got business hours schedules support analytics we're gonna get into a lot more about that so here's where we're headed we'll look at the key date functions that you use in formulas we will look at leap years and I think I've realized I'm the only person in the world who associates a frog with leap years but just go with me on it we will look at conquering leap years in date formulas and then finally we will turn our attentions to the new time datatype and how that plays out in formulas but before we look at the date functions let's just level set on what we're talking about so we have three data types today is a tale of three data types we have date we have date time which you should all be familiar with and now new kid on the block time these are three different data types and it can get a little bit confusing especially between date time and time so we'll get more into that toward the end but just to level set these are three different kinds of data we have a date in isolation a time in isolation and then the conjunction of those two a particular time on a particular date so formula function machine this is my favorite analogy when it comes to formulas all these functions I like to think about them as a machine you give it an input and you get an output and Astro goes tada uh-huh so who's ever seen this one before when you've been writing a formula I expect way more hands than that you're all liars so when you go to create a new formula this is the first thing you're presented with you have to say what data type you are going to craft your formula to return as that ultimate output okay so you can do up you can work with other data types within your formula but ultimately it's gonna return one particular data type and you have to declare that upfront it's literally the first thing it asks you when you go to make a formula okay so choose wisely because once you get in there you can't change it can always start over though and I've done that many times so let's come back to our inputs and our outputs this is des and it should show two empty brackets this is where things are gonna get a little funky with my font issues and I apologize so what is the input type for the day function who knows just shout it out it's a date you give it a date what does it give you in return what's the output I heard it a number okay so we're just gonna review a whole bunch of days in quick succession so a day the day function you give it a date field and it's gonna give you a number so what does that look like if we give day March 28 2018 that's today if my mouth is what are we gonna get out of that formula 28 all right how about months what do you give month a date yeah these are all very simple you're gonna get this all right and it gives you a number so if we give months March 28 2018 what are we gonna get back everybody together excellent all right here you can imagine the same thing you give it a date you get out a number so we get bit March 28 2018 what are we gonna get 2018 awesome all right how about today what do we give today whoa somebody had it really quick nothing trick question we don't give it anything and what data type does it give us in return a date excellent your experts how about now hey you gotta first oh hi haha we give it nothing and it gives us a date time you all had it you were just on opposite sides all right how about date what do we give date three numbers you got it over here we give date three different numbers and we get back a date as we might expect so if we give date 2018 three and twenty eight note the order of those what do we get back March 28th 2018 okay so that's how we're gonna construct a date out of numbers and you can start to see how you can piece these together and use them together to do cool stuff how about date value we give it a date time excellent somebody over here when we get out what datatype do we get out as our output a date so we give it March 28 28 teen at approximately 110 I hope I did that math right yeah and what are we gonna get out just the date part you got it okay so we're stripping bits of our data out so take this first bin now let's take our function machine first bin if we do year today year of today what are we gonna get out 2018 awesome alright this one looks a little more complicated when you start to see something like this I like to work from the inside out alright so let's start all the way inside we have now and then we're gonna take the date value of that and then we're gonna take the month of that so what are we gonna get out awesome you guys are great all right so when we come to actually putting these together to do interesting formulas with our dates people often say but math is easy Beth why do we need to worry about this so if we have October first plus five if we're gonna get October 6 right as you would expect however oh no all right sorry so if we have October first plus 364 what are we gonna get oh you're getting ahead of me mr. ray what do we get we're gonna get September 30th right but what if Auto BIR 1st of October 1st 2015 if we do plus 364 we're gonna get some time 429 what happened to September 30th it got eaten by the leap year all right so let's turn our attention to leap years let's figure out how to conquer these leap years and our formulas so without shouting out any of the rules how many of you know exactly when leap years happen and could tell me the rules if I actually asked you to I see like one two hands that's about what I expect I'm gonna tell all the rest of you a secret is that you're okay because you don't actually need to know any of the rules for when leap years happen to conquer leap years in formulas I'm gonna show you three rules and if you obey these rules in your formulas you don't need to know when leap years actually happen and your formulas will always behave around leap years and you will never end up every four years with all your date formula is getting all wonky so our three rules inputs/outputs timespans any date used in your formula might be a leap day somebody is born on February 29th right that's gonna happen it does happen somebody joins your organization on February 29th it's gonna happen you need to account for that eventualities outputs the result might be a leap day right the output might fall on a February 29th we're gonna account for that and then time spans ending a period of time reference to that plus 364 that we just did it spanned a leap day and we have to account for that okay so if you follow those three rules and I'll walk you through a few formula formulas to show you how to do that your formulas will behavior on leap days so let's dig into our first one I'm gonna refer to my notes so that I do not miss the peak so let's break this one down we're gonna calculate we're gonna do that plus 364 idea that we had in our example right except we're gonna do it right so the idea is adding one year - one day - a date so right off the bat on the first line we're gonna check if they joined on a leap day so if the month of our join date is two and the day of our join date is 29 see how we're starting to piece together these functions that we reviewed and if that's the case you all know how an if function works I think right if this then that otherwise that okay so now we're into the than that so if they joined on the leap day we need to add 365 days we know that that's going to give us February 28th on the following year otherwise that if they did not join on a leap day then what we're gonna do is take their join date put it into the following year that's the year joins eight plus one and you see we're wrapping that in a date function we're giving it three numbers and then there's that little minus one at the bottom we're gonna back it up add a plus one year minus one day and that way we don't need to know if that year spans a leap day we're not saying plus 364 or 365 we're just saying take that same day in the following year and back it up a day that's their expiry date okay so that is a rock-solid formula that has a lot of patterns that you can apply in your orgs to make your date from date formulas impervious to that leap year gremlin so this is a good stage to take a picture if you want with the highlighting yeah you never hit that if they joined on February 29th if they joined on February 29th you only hit the red yeah know it yeah no worries all right let's look at another example we want their birthday don't take a picture right now this is a terrible formula this is the cautionary tale so we want to calculate one of your constituents birthdays right but we want their birthday this year but this is the bad way to do this you might think okay we just take the month in the day and we put it into this year year of today but what if their birthday has already passed this year then that that's an irrelevant birthday right and what if they were born on the leap day and this isn't a leap year that's gonna give you a big fat error okay so let's think about this differently let's think about next birthday that's what's relevant I need to know when to touch this constituent right on their next birthday so this one looks a lot more complicated but we'll walk through it so we start again checking for that leap day so if the month of their birthday is two and the day of their birthday is 29 hey they're Liebling did you know that that's the word for someone for in the leap day their elite fling so there leave your baby so if that's the case we're gonna check for something else is today great our sari is the 60th day of this year greater than today so we're basically checking if we've already passed what would be their birthday which is like February 29 or March 1st so in this way by using just the 60th day of the year so January 1st plus 59 we don't have to know if this is a leap year right we're just saying the 60th day of the year it's this magical day and so if the 60th day of the year which would theoretically be their birthday is greater than today so we know that their birthday is still in the future greater than this future when you're dealing with dates right so if that's the case wrong directions we are going to take hi guys I want to make sure I say that's right we're going to take the year of today and so their next birthday is the 60th day of this year that's what this is Nick so the date if the top line is there's 60th day of this year and the bottom line of the purple is the 60th day of next year right because we were checking if their birthday has already passed this year sorry if they're not a Liebling so now we're into the second half of the first if statement following me so we're in the second half of the blue if statement we're gonna do the same thing but we're just checking against their actual birthday instead of the 60th day of the year magical date okay so by pulling out this exceptional case of leapling and using the 60th day of the year we don't have to know if it's a leap year so it's a same pattern putting it either this their birthday and this year or their birthday and next year depending on if their birthday has already passed all right and I'll post these as well alright you made it through that's another rock-solid formula with some good patterns 60th day of the year you don't all remember that now so to conquer leap years which you will now also see it with frogs you have to worry about your inputs your outputs and your time spins easy enough all right so before I turned my attention to time this is actually an upgrade since I gave this presentation at Dreamforce last year our formulas function machine has gotten a really super awesome upgrade we now have week day I'm so excited about this ok I'm such a nerd what do you think we give weekday a date what do you think we get out a number that represents the day of the week it's so exciting ok sorry so if we give this March 20th I'm gonna show you why this is so exciting in a second and you're all gonna agree with me if we give it March 28th 2010 what are we gonna get out and Europeans I'm sorry this is a bit North American centric what are we gonna get guesses I heard it over here we're gonna get four so we start counting on Sunday sunday is one sorry sorry Europeans so how many of you have seen or used this pattern in your formulas before thank you at least a few hands shooting up of those of you who just raised your hands did you actually understand what you were doing thank you honesty I love it she shook her head vigorously no this is a pattern that shows up in a whole bunch of date formulas that are out online and arcane official documentation in blogs everywhere and all this does is what that weekday function does so we don't have to mess with this anymore you don't have to be like I think this is I'm just gonna copy and paste this and trust the blogs no so this you're gonna get a bonus you're gonna learn case as well if you didn't know this one but in the past that line that just has a weekday of my date we had to have this whole bonkers ness right and we don't anymore how we get to just do this so what I want is to turn that birds that the weekday function returns into the the word of the day of the week this is how we do that so this is a what's the output return type of this function text this is a text formula but it's gonna take a date field and return the word of the day of the week that that lands on okay so case is check this thing and then if it equals this do this equals this do this this do this it's a whole bunch of pairs that's why the line breaks work like this okay so we're gonna do case we're gonna look at the weekday of my date and then we're gonna do all those pairs and then case has to have a like if it doesn't equal any of those things what should I do and we're just gonna return a null string and then what that returns is this yay I was so excited about that all right one more in our first upgrade to our formula function machine add months this is so exciting for admins I'm sorry developers have been able to do this in Apex for years add months three you're done right but admins couldn't do this until the winter release so you can do this now you have the power you give it a date and a number and it gives you a date just like you would hope I hope yeah so if we give it March 28th 2018 and three separated by a comma in the actual syntax what are we gonna get June June 28 2018 okay so let's just take a look at what that means yeah I'm gonna get there you're ahead of me it was a great question what happens if it's on the 31st I'm actually gonna give you an even more exceptional scenario so add months my date comma three this is what it would look like in your formula and you would get this so it's a date formula in this case called next quarter pass in that top date my date and I get 628 and the question was what happens if it's on the 31st well I'll give you an even better one what happens if it's February 28 if it's the last day of the month it assumes that what you care about is that it's the last day of the month ok so this is a little bit you know there's some behavior there for you to think about carefully as an admin before you use this but um that's what it'll do okay so if it's February 28th and 2018 is not a leap year so that that's it that's the last day of the month you will get May 31st May 30 30 wait no that's right 31st yeah no it's I just used February as the example to make the point that it's if it falls in the last day so like what's a September thirtieth add one month is gonna give you October 31st it won't give you October 30th even though that it does exist so it's gonna give you the same day in that month unless it's the last day of the month it'll just give you the last day of that month all right yeah that's a great point that I really should have pointed out you can put that three could be negative that'll take a negative three yeah great point yep so are you telling me that that formula I had earlier is actually totally irrelevant now and I could just do admins 12 and then subtract today I bet you're right somebody give us a limited job I bet you're totally right now I'm going to test that yeah I might have to update this again so yeah go experiment with that but yeah this is this is huge for admins right this was on the idea exchange for a long time all right let's finally catch up with our new kid on the block time so a few of you a bunch of you actually said you already have time fields in your orgs and that is awesome time has been a long time coming it was pilot last summer you had to talk to your AE it was private beta and winter you still had to talk to your AE it was public beta in spring and it is going GA in summer a teen forward-looking statement but it should be GA this in this summer release so you can play with it now definitely play in your sandbox is first but you can get it in production as of spring all right and it is public so when you go to create a new custom fields you see this little time and it does still say beta nice red tag to remind you that it is a well I'll tell you what exactly what that means in a second so long time coming this screenshot is actually about a year old and currently will say 11 years ago if you go to it so I really want to meet Jonathan Chi Ling Curt I just think that would be cool see what he thinks about this finding kidding I hope he still uses Salesforce I don't know so this is a screenshot from a project I did a couple years ago where we really needed time and I I see it nodding heads like we had to use these crazy pick lists and these are just text they're useless it's terrible and what this looks like on the back end this is only part of that like it keeps going like that's a maybe a third of the values that you have to maintain as an admin this is terrible so what we have now is like actual time fields this is so cool they won't look quite this nice until the summer release this is a summer 18 org that I took this screenshot from enlightening they look a little funky right now cuz they're just showing you the GMT value but in summer and this is what they're gonna look like and they're gorgeous and you note what's different on the right 24 hour time so what I did was I changed my locale to English UK and it's just it just displays it in English UK style it's it just works it just works it's great I was super impressed with that so this is what beta means it is a high quality feature with no limitations so please go to the Trailblazer community there is a custom time field group I think that's exactly what it's called and you can read there and in the release notes as well what those no limitations are they are fewer and fewer with every release and it will be GA and summer alright so this is our second upgrade to our formulas function machine we have new functions that come with time so what do you think we pass our this might feel familiar month/day/year we now have our minute second alright so what do we give our time and what do we get out oh no so if we give this 11:30 what are we gonna get out 11 all right minute same idea yeah we're gonna get there and I'm not gonna have good news I'm sorry all right sort of so if we do a minute we give this we're gonna get 30 and if we do second we get time number it's gonna give us zero yes you would do uh you're you're too ahead of me wait so time now is same thing as now I'm gonna rush a little bit because we started late um so time now give it nothing get out of time of like right now time value is what you're looking for yeah so you give time value a date time and it strips off the time portion just like date value stripped off the date portion so if we give so here's where it gets funky if we give it this time or this date time rather we will get 1134 in 17 seconds but note the GMT so here's where we're gonna talk about date time versus time this is date time note each clock is showing a different time but it's all the same moment right it's all the same moment in the universe so that I like to call it so you need to know the system will render a date time in the local time it knows the offset of your user it knows what time zone your user is in all of that this is time it is a clock with no batteries it is a clock that you take the thing in the back and you set it to a certain time when you say 11:30 everywhere doesn't matter where I take that clock I can fly that clock to London it's still gonna say 11:30 right so think about time as a clock with no batteries so if you're coming from date-time or you're coming from now or time now the system has to make an assumption about where you are right so you're gonna get GMT Blunden UTC whatever you want to call it okay so that sort of answers your question but let's we'll look at what that looks like in a formula and how you actually account for that okay so let's test some of these out our of time now twenty right cuz it's GMT so it's thirteen because one o'clock one pm 1300 hours plus seven to get us to London so 20 is what that returns okay so we gotta be a little bit careful let's look at a couple formulas calculating total business hours so on an account you have a time open field and a time closed field pretty good use case for just a straight time field that's not a date time they open at that same time every day right it's just a clock so how many hours are they open each day time close - time open / I can never remember 3,600,000 it's hard to read without the commas but you don't have commas in a formula um so what's that's 3,600,000 thing about anybody know somebody's use some a few of you said milliseconds so I'm trying to return this in two hours right and so one hour is 60 minutes is 3600 seconds is 3.6 million milliseconds so admins get ready to get really good at millisecond math write that down on a post-it note and stick it on your desk because you're gonna do it a lot all right so pretty straightforward though right if any of you saw this presentation at Dreamforce it was not as straightforward and that really so we've had some it's great so you can also do and I don't have an example for this one but you can also do a time plus or minus a number so if you wanted to do like you had an open time and then a duration and you want to calculate the closed time right like do it that way instead the number that you add or subtract from your start time is also in milliseconds so you also have to account for that in your formulas okay so you can also do a time plus or minus a number of milliseconds and then it will return a time pretty handy oh so this is what that would look like all right 8:30 a.m. close at 5:00 p.m. that means they're open 8.5 hours yes yes yes if you just the question was if you just did time close - time open the answer would be in milliseconds which I think you would figure out pretty quickly what you did is like wow that's a lot of hours all right let's do one more example check if reps are calling customers at their preferred time so in this scenario we're gonna imagine that on a contact we have a preferred time field that represents the top of the hour that they wish to be called at all right and we want to check if they're creating cases outside of that contacts preferred time it's a relatively contrived example I grant you that but just so with our formula field is going to return a check box and we're gonna compare the created date of the case to the preferred time off the contact because we're gonna use time value of the created date so this chunk in the middle is just to get that created date into the local time for GMT - 7 which is Pacific so this one does not support multi timezone orgs it also doesn't support daylight savings time so there's potentially some work that's gonna happen around returning the time value of a date-time in the local in the users local time massive forward-looking statement on that okay it's on their radar it's on its on the team's radar we know it would be valuable but this is what we can do now it does work so we're gonna take all right hang on so when we do the hour of the time value of the created date we're getting that GMT number so we subtract seven to get that to the local time but because clocks don't account for negative numbers they don't go into negative numbers but they rather go back around to 24 we have to first check if that made us negative less than zero negative and if so we're gonna add 24 to that and then subtract seven otherwise we'll just use that GMT value minus seven so all of that just to get the local hour and then we just compare it to the preferred time off the contact and if they're equal this field would be false and if it was outside of the preferred time it would be checked and you would slag it has a bad case so that make sense I know the local time thing is not awesome but we live in the future so get creative in your sandboxes be very careful mixing date time and time in formulas because we have to account for our time zones and then please please please provide feedback and share ideas and tips in the trailblazer community group for a custom time awesome that is all i have thank you for coming I I you were awesome vote asking questions as we went but I would be happy to take more questions yeah over here okay yeah in summer it shows the time I believe yeah it should test it way as soon as you can get into a sandbox preview of summer I I haven't checked but I think it should work

Channel: Salesforce Developers

Views: 5,320

Keywords: TDX18, trailheadx, trailheadx18

Id: wciBI5rQvHY

Length: 34min 34sec (2074 seconds)

Published: Wed Apr 18 2018