Excel Fun: Date string to real date

Well, it’s not really that fun. Sometimes I need to manipulate data directly in Excel (or a SharePoint list) and don’t necessarily have the luxury of using additional columns for holding intermediate step values.

For example, a worksheet (or SPS list) has a column that “looks” like a date, “4/1/2018” (US formatting, M/D/YYYY), but the data isn’t treated as such in Excel. It’s literal text and things like sorting and other Date/Time functions, like DATEVALUE(), just fail.

So, time to hack on a “text to date” formula. It isn’t going to be nice since it has to do everything all at once, but for readability I’ll break it down.

First off, I’m going to use the “DATE” function to do the final string->date conversion. This takes the form DATE(year, month, day). Now to figure out each of those components!!!

Let’s assume the date string to convert is in A2 and currently formatted M/D/YYYY. The easy part is getting the year. It’s just the right 4 characters. Simple!

"Year: " & RIGHT(A2, 4)

Next is the month. That shouldn’t be too difficult. It’s gotta be some sort of LEFT string function, right? Yeah, the form for the function is LEFT(text, [num_chars]). Months can be either single (1-9) or double (10-12) digit numbers, so can’t hard-code [num_chars]. But, we can take advantage of the consistent format and use of delimiters, namely “/”. Using FIND(find_text, within_text, [start_num]) we can determine what position the first “/” is at and do a little math to get the actual character length we need.

"Month: " & LEFT(A2,FIND("/",A2)-1)

Heck, this hasn’t been too bad, so what’s the big deal with getting the day? Well, it’s in the middle of the string and as noted above, the month can be 1 or 2 digits and the day can be 1 or 2 digits (1-9 and 10-31 respectively), so again, no hard-coding values. Not many choices here, either MID(text, start_num, num_chars) or some weird combination of LEFT(text, [num_chars]) and RIGHT(text, [num_chars]). For simplicity, go with MID. This is primarily because we can easily get the start_num value of the first char after the first “/”, namely FIND(“/”,A2)+1). num_chars is where the pain is, but FIND will come to the rescue (along with a bit of math)! Basically, num_chars is the total length of the string minus the length of “month/” (actually the index of “/”) minus the length of “/year”.

"Day: " & MID(A2,FIND("/",A2)+1,LEN(A2)-FIND("/",A2)-5)

Do all the appropriate replacements in the DATE function and voila, one single ugly but useful function.

Wait!!!

There is a presumption regarding the length of the year. What if it’s 2 digits, (e.g., 4/1/18)?

Not really a big deal (sorta, more on that later). In the process of accommodating for both 2 and 4 digit years, we can also get rid of any “constants” except to shift left/right from a delimiter. The new and improved “day” function: moves 1 char to the right of the first “/” and stops at 1 char to the left of the second “/”.

"Day: " & MID(A2,FIND("/",A2)+1,FIND("/",A2,4)-FIND("/",A2)-1)

Since this adjusts for both “year” formats, we should probably take a similar approach to getting the actual “year” value. Just a matter of taking all the characters to the right of the 2nd “/”.

"Year: " & RIGHT(A2,LEN(A2)-FIND("/",A2,4))

The one caveat here is that some applications (e.g., Excel) will presume that when working with a 2-digit year, it is at the beginning of the “known IT universe” which starts at 00:00:01, Jan 1, 1900, so in the example provided (4/1/18), the year “18” would be converted to 1918. Depending on context and data set, this may be easy to work with; just prepend the calculated value with “19” or “20”. If spanning both 20th and 21st centuries. well, good luck 🙂

Advertisements
Posted in Uncategorized | Tagged , | Leave a comment

Where Are My Scripts???

A couple of things are going on somewhat simultaneously in my little world (more like over the last year or so, but who’s really checking?).

Anyway…

  • CodePlex is officially dead (well, archived).
  • I have decided to move all my blogging (what little there is at this point) to here vice my TechNet blog (page will eventually be unreachable) and blogspot (which I “thought” I would use for personal stuff and had to use for an MIS grad class at the U of Arizona). Don’t even go there with the whole fiasco with MSN/Live Spaces and the posts I lost!!! (yeah, sore subject)
  • I have PowerShell scripts, etc. that need a home on the internet instead of my local machines and various hard drives which will hopefully make finding, sharing and updating them soooo much easier!

So, I’ve decided to drop my scripts on GitHub. For now, it looks like most will be gists, but eventually some will be full projects in respective repositories. As I add useful versions, I’ll post with appropriate links.

Enough rambling for tonight; I’m beat and the two beers I had are definitely having an impact at this late hour.

Posted in PowerShell | Leave a comment

I’m Here!!!

With the passing of time, things change. It’s inevitable. In IT, this seems to be even more accelerated than the “real world” and very little if anything can remain static and relevant at the same time. Some historians believe that purging these “dead” artefacts from the digital world may be detrimental to our future generations. Unlike stone tablets, papyrus and books that provide contextual reference and cross-correlation, this won’t be the case for the untold amounts of digital information that are deleted. Those helpful hints, snippets of conversation, banter, etc. are just gone, leaving holes in the connectivity of our cumulative history.

Wait… This is supposed to be a technical blog with the occasional personal spin. Where is my brain?!?!

After that weird little tangent I have to refocus on the issue at hand and it basically comes down to this…

For a number of reasons (that will eventually come to light), I moved my stuff from my TechNet blog to here.

Party on Wayne!

Posted in Uncategorized | Leave a comment

Where Has The Time Gone

Almost 7 years since my last post. I guess you would think this is an abandoned site. Not quite. More like neglected – I’m kinda busy. So, to recap:

I spent the rest of summer 2011 in Kabul and was back home for Labor Day Weekend. Like that last post in 2011, at my going away party a few days before I departed, I swore I would never go back to AFG. I was DONE! And I meant it.

Worked from home for my then employer and late summer of 2012 things took an interesting turn. I transitioned to another company and started working for the state of TN the week after Labor Day Weekend (staff aug on a very cool team doing arch design for state depts/agencies). Didn’t last long though. Early Oct I got an offer to go elsewhere… yep, back to Afghanistan. This time under NATO (NC3A/NCIA to be specific). Not sure what the attraction was – well, maybe the money. Money is good to have.

Planned on doing the gig for 2 years, but ended up bouncing around AFG for almost 4 years. Met a lot of great people from all over, lived in oversized plywood boxes, conex containers and hardened buildings, experienced numerous rocket attacks (they suck and I know people that went home in boxes) and hopefully helped save lives (supporting the Patient Evacuation Coordination Center).

But, I got burned out, Krista got burned out and our Girls needed me home. So I managed to somehow get hired back at Microsoft.

Flew home Labor Day Weekend 2016 (what is it about that weekend?) and hit a little snag during my travels. About halfway through the 14 hour flight from Dubai to Toronto, my body decided it was time to start pushing a small rock through my kidney. Had no idea; felt a lot of discomfort, but no real pain. Eh, figured it was the perils of traveling and working months on end without a break. That all changed early in the morning while waiting for my connection to Nashville. Discomfort turned to pain then “oh crap, I’m a grown man crying like a baby” pain. EMT showed up and threw me in an ambulance plane-side of the terminal. I don’t take drugs unless absolutely necessary (it takes a lot for me to even take Tylenol), but after the shot of morphine, I can somewhat understand how people can become addicted. Still had pain, but didn’t really give a sh!t.

Needless to say, I missed my flight home. Spent about 6 hours in an ER with IV and more med and was discharged. Strange; just let go to do whatever. And not properly stamped in my passport (this has always bothered me). Cabbed it to a pharmacy, picked up my Rx and off to the airport. Oh, them Canadians, they have that medication payment thing down. Even as a non-Canuck, my meds cost like $60 USD, including pain meds. Would have been a couple hundred at home.

Back at the airport, I managed to get my gear that I left behind (like my camera bags!!!). Apparently wasn’t a priority to get everything in to the ambulance. Boarded my flight and FINALLY made it home.

For that first week, I kept looking around and thinking, “For the first time in years I’m really ‘home’.” The home we had built the previous year. The home I hadn’t really lived in. In a great neighborhood, across from the pool, a quick 5 minute drive from my daughters’ elementary school, far enough from congestion but close enough to be at the mall and civilization in 10 minutes. It was a difficult decision, but the offer from MSFT to relocate to the DC area just didn’t feel right. Asked to not follow through on that and everyone was ok with it. Hmmm. Well that was good. Two weeks later I was a blue badge once more – with my old alias, yeah!

A year and a half later, I’m working from home with some travel to customer sites. I get to be an active participant in raising our daughters (we had another girl Nov ’17) and I gotta say, I really can’t complain.

Well, that’s enough for now. Let’s see how long until I make my next update 🙂

Not sure if I’ll post technical stuff here, for now it’ll go on my MS blog, https://blogs.technet.microsoft.com/antgut/.

PS, I had the kidney stone removed in late October 2016. Sucked for a couple days, but recovery was relatively quick!

Posted in Uncategorized | Leave a comment

SPS 2013, Host Name Site Collection Caching Fix in KB 3114945

Short and to the point…

KB 3114945 (April 12, 2016, update for SharePoint Foundation 2013) lists the following issue as being fixed:

Assume that you sign in a host name site collection and then other site collections in the same web application. If the host name site collection is deleted and the cached user token is still valid, you receive an error message on later attempts to access remaining site collections.

What the article does not include is the method for enabling this fix!!!

To do so, execute the following:

# This setting affects the whole farm.
$sts = Get-SPSecurityTokenServiceConfig
$sts.WindowsModeIgnoreCache = $true
$sts.Update()

Thanks to Joe Rogers for the info.

Posted in SharePoint 2013 | Tagged , | Leave a comment

Announcement: Upcoming Release – Daughter 2017

Not necessarily “tech”, but figure this would be a good “first post.”

So, about 4 months ago (late June), I sent the following email out (MS-internal)…

Proud to announce that Casa de Gutierrez is well over a third of the way through developing Daughter 2017. Official RTW (release to world) is Dec 5, but as with previous products (Daughter 2008 and Daughter 2009), delivery will be scheduled 1-2 weeks earlier (sometime between Nov 20 & Nov 30). Final date to be determined by delivery management team with consideration of product maturity, holidays and other contributing factors.

At that time, potentially a few days prior, I will commence work-stoppage and assume responsibility for all CDG operations; to ensure developer has no distractions, existing products are taken care of and CDG facilities are in proper order. This period of non-work will be approximately 6 weeks; resumption of “work” is tentatively scheduled on or about Jan 8, 2018. As it is still early in to the dev cycle, post-release product support requirements are unknown and limited staff on hand (no parent companies to rely on), this time-frame may change. Further details will be provided as new information is gathered.

All concerned parties are very happy. D08 & D09 are very excited about D17. They did not want a dissimilar product type; this would have apparently caused major incompatibilities.

Note that the developer is doing well (no ill effects thus far) and product has met all milestones. Expectation is that this will continue to be the trend for the remainder of the dev cycle.

A bit scary, but we are now 31 days out from RTW (Nov 30)! Work-stoppage officially starts Nov 17, so we will hopefully be prepared. Otherwise, everything appears to be on track and good to go. Exciting times!!!

More to follow…

Posted in Uncategorized | Tagged | 2 Comments

Where Do We Start, Where Do We Begin?

[2018-09-28: Imported from blogspot]

Hmmm…

Another place to post my thoughts. Just what I needed…

I guess the first thing to do (as is the custom) is to rattle on about myself, the blog version of “Hello world.”

I was born in California and not long afterward experienced the first Man walking on the moon. Not that I remember it, but it gives context on my age and the era that I grew up in.

For my entire life I have bounced around, never living any place more than a couple of years. Five years being the longest when I was in the Seattle area and that was in three different locations. Come to think of it, the address that I’ve had the longest as an adult is probably where I live now. But that is a little off considering that I’ve spent over half of the time in Afghanistan (more on that later).

Growing up it I moved because my father was in the US Marine Corps; enlisted, retired as an E-8, Master Sergeant. As an adult (after graduating high school) it has been due to changing schools, changing jobs and life changes.

I’ve been doing computer-related work in some capacity most of my life although it was never really my intention; I wanted to be USMC pilot. I wrote my first “real” program in 1983 when I was taking an elective class that met before school actually started, that dreaded zero period. It was something to do and “mostly” kept me out of trouble. Yes, my New York classmates (Woodland Jr. HS, East Meadow HS), it was I that had the shaving cream and decided it would be better in someone’s locker than the can and got us kicked out of the building. Sorry about that. Shoulda owned up to it.

After another move (my parents’ last) I graduated from HS and went off to college. Apparently someone thought I was smart and I attended the US Naval Academy. Nope, not so much. Well, maybe; I was in honors, AP classes and all that so I did have some brain power. I just wasn’t in the right mind set to be there (trouble on the home front) and my academics suffered. They could have kept me, but unknown forces swayed that decision. Ok, being of Mexican heritage and an enlisted brat who didn’t deal with BS games was not working in my favor. Could have fought it, didn’t. Ejected with my DD-214 in hand.

So I went back home. Attended AWC in Yuma, UA in Tucson, back to AWC and finally graduated from UA. Yes, I had a good time in college and was asked to not attend (UA the first time) because of it. After 8 yrs of taking classes, etc. most people are addressed as Doctor. In the end I only got my BS (Major: Mathematics, Minor: Astrophysics). Teaching was an option, but the money wasn’t (was married with a child) so I became a computer geek at Ft. Huachuca working for DISA. A couple years there, then off to Microsoft. 5 years later I hit burn-out and headed off to VA. Worked for the US Army for a bit, then a long-term engagement with a good company. With them I traveled to some of the finest destinations: Fort Riley, Fort Stewart, Hattiesburg, Fort Campbell, Kuwait, Shaw AFB. Interesting to say the least.

It was during my stint with 101AA in KY prepping the division G6/S6 sections to deploy that I met Krista (my wife). After about 6 or so weeks I asked her to move to VA with me. She did! 5 weeks later I left her alone in new town away from family so I could go to Camp Arifjan, Kuwait. Yeah, I’m a heckuva guy. It’s worked out though. 5 moves, three states, a number of deployments to Afghanistan and a lot of stress later, we are still married with two beautiful daughters (and 3 dogs).

Now, Krista and my Girls are back at home and I’m working in Kabul for NATO (ISAF Joint Command to be specific). I’m trying to stay sane, but doesn’t always work out well. 12+ hour work days, no days off and working on my grad degree. Sleep should be optional, but I tried that for a couple days and the results weren’t pretty.

Enough rambling for today. I have a post to do for homework (MIS 587, Business Intelligence).

Obligatory plug: anyone interested in my work history/background can view it on LinkedIn.

Posted in Personal | 2 Comments