Ok I ummmmed and arrrrred over this one heaps and I am still not sure it is interesting enough to post about, however I guess all blogs are interesting to someone so I thought I would give it a shot and see if it crashes and burns.

See gifs will make it funner! ^

Ok so the reason for this is alot of my job is cleaning and reformatting data so I can actually do something with it!

One of the most annoying things to work with is date, especially dates with time codes in them as often you want a daily summary and not a break down of every minute of the day.

The Issue

Ok as you can see this data is a time stamp, so it captures the date and time.

If I try to do a daily tally it is going to look really weird

Step 1

Let me introduce you to the date function, this handy little guy takes in three values, the year, the month and day and spits out it as a date that excel can read.

For example if I wanted to do todays date I would write =DATE(2022,5,2) and I would get..

Step 2

Ok so we know how to create a nice clean date but how do we pull out the year, month and day from this huge date code like this 01/04/2022 9:30 AM

Luck for us there are 3 really cool functions that pull out the year or a month or a day from a date function

Funny enough the names of these are

=Year()
=Month()
=Day()

As you can see you point at at this big blob of date and it pull out what we need!

Step 3

Now we have the year, month and day and know how to make a date out of year, month and day, we can make a clean date!

The final result!

Ok so now we can apply this approach to all out dates, here i have pulled the equation into one line to clean it up but feel free to spread it out and double check the first few times