You Think You Have Time

With every passing second of every minute, of every hour – you are in a constant race against time. Trying to find time to exercise, to spend time with friends or family, or read the latest Excel TV article. Managing your schedule becomes even more important in the age of information. More data means more analysis, which means more reports. Either you beat the clock, or the clock beats you. But not everything is what it seems when it comes to time…


Download Workbook


Function: INT

Describe It:

  • Round a number down to the nearest integer

Syntax:

  • =INT(number)
    • Number is the value in which to round down and return with no decimal places

Make It:

=INT(B3)


HOLD ON, I THOUGHT YOU SAID

THIS LESSON WAS ABOUT TIME?

It is, we just need to be a little bit more inventive with our use of functions once again.


Taco Bout It:

  • Excel dates are any zero or whole number value ranging from 0 (1/0/1900) to 2958465 (12/31/9999)
  • Excel times are any decimal value ranging from 0 (0:00:00) to 0.99988426 (23:59:59)

If the INT function removes decimal numbers (time values). To extract the time value we would want to take the (Date Time) minus (Date).


Make It Again:

=B3-INT(B3)


Make It Even Better: (Courtesy of Brian Canes)

Function: MOD

Describe It:

  • Returns the remaining amount after a number is divided by a divisor

Syntax:

  • =MOD(number, divisor)
    • Number is the value in which to divide
    • Divisor is the value in which to divide by the number

Make It:

=MOD(B3,1)

Talk It Out:

With MOD (Modulo) the function wants to see how much of your value is remaining. For instance if you were to try something like =MOD(10,3) you would see that 3 can go into 10, 3 times before it can no longer be used. At this point you have ( 10 – ( 3 + 3 + 3) ) = 1. In the above instance you are using 1 as the divisor to remove whole numbers (Date) and all that remains are decimal places (Time).


N-T-S

(NOT TOO SHABBY)

I know what you’re thinking: if only data was that clean all the time, right? Sometimes what you think are dates and times are actually just one long string of text that is impossible to format properly.


23DEC2016 15:38:56


Function: DATEVALUE

Describe It:

  • Returns a date value from a text string

Syntax:

  • =DATEVALUE(date_text)
    • date_text is the value in which to round down and return with no decimal places.

Make It:

=DATEVALUE(B6)


Function: TIMEVALUE

Describe It:

  • Returns a time value as a decimal number from a text string

Syntax:

  • =TIMEVALUE(time_text)
    • time_text are values ranging from 0:00:00 to 23:59:59

Make It:

=TIMEVALUE(B6)


If you don’t respect your time no one else will.


You are the expert. You are the one that everyone comes to for help. You get people out of the last minute jams. People can respect your skills, but if they don’t respect your time you will never truly get anything of value accomplished. Always set time aside for yourself. Use a Tomato Timer to break up the daily monotony. Don’t be afraid to step away from a project to gain clarity. We only have a finite amount of time on this planet. Do not spend each passing second entertaining the distractions.


So what are you waiting for?

86400

10 thoughts on “You Think You Have Time

  1. The fun comes when you have text in a non-standard format: 1/7/2016 1:25 AM

    You have to create a parser to convert it to a format Excel understands before time is off your hands.

    1. Ain’t that the truth! I figured I’d need to dedicate a full write up to TIME, LEFT, MID, RIGHT, FIND and Text to Columns to truly do it justice. I’ll dive deep into the tool box soon enough here for a full on data normalization tangent and maybe pull in some guest bloggers for that one that are professionals in the field. The amount of unusable data out there blows my mind though, we come together to set programming and computing standards yet the one thing we fail to regulate is our best asset – DATA.

        1. I plan on getting into NETWORKDAYS, NETWORKDAYS.INTL, DAYS and a million more of Excel’s fun little offerings in due time – if there’s some obscure ones you might want me to hit on please let me know. Also thanks for checking out the post and I went ahead and threw you a shout out above for your help.

  2. If A1 contains a Date-Time then
    the date = INT(A1)
    the time = MOD(A1,1)
    (No need to subtract)
    Regards
    Brian

  3. Hi Alex

    Really like how well organised this article is, it is all clearly explained and well set out.

    Definitely agree with taking a break from a project on a regular basis. Sometimes it feels like the wrong thing to do but that bit of space can help you come back with fresh eyes (and a fresh mind!) to solve the problem you’re looking at. Works for me all of the time!

    Thank you- I’ll look out for more articles from you.

    Ian

    1. Thanks Ian! I think being conscious of when you’ve gone too far down the rabbit hole is a great skill to have. My greatest place for clarity seems to be when I’m driving in my vehicle. Something about the windows down, music up and no immediate access to a workstation in those moments something just *CLICKS* and a solution comes charging out of nowhere like a freight train.

  4. Mod is one of the best functions.

    Ones I really like:

    Developing Projections or Roll-ups:

    =CHOOSE(MOD(ROW(A1),3)+1, PROJECTION1, PROJECTION2, PROJECTION3)

    where you allow for multiple projections based on rows. You can have one option refer to previous cell references allowing you to tack on summary row for the projections you determine. With OFFSET, you can create some really strange projections or dynamically resized ones (I think) if you add matrix multiplication to it. random between also cool in this context as you can vary the projections and even skew it if you would like.

    With INDIRECT, you can use MOD to dynamically generate column values or row numbers. With ROUND you can cause duplicates to spawn. As in the below, which duplicates each row twice:

    =INDIRECT(ADDRESS(ROUND((ROW(A1)+1)/2,0),COLUMN(A1)))

    Then you can also do inversion of a matrix via formula:
    Something like: INDIRECT(ADDRESS(COLUMN(A1),ROW(A1))).

    Combining these concepts, you can take any N x N matrix and mold it to matrix of equal entries M X M. Even create duplicates that get manipulated in different ways.

    This kind of reminds me of R data frames when being munged by different data manipulation frameworks like Shape (disclaimer haven’t done R in long time).

    1. It must be a spreadsheet developers intuition because I was needing something similar for a rolling target and determining how many months in a row it has been performing – I’m going to read this about 10 times through to let it sink in and get to work on it. Thank you for such a thorough write up Chris.

Questioning My Mental Health?