Excel Dates and Times Article

Have you ever tried to do some type of calculation with a time value in Excel, only to receive a strange result? Two examples are adding up hours on a time sheet and multiplying the number of hours worked by a pay rate. If you’ve tried something like this and encountered an unexpected result, the solution is fairly simple.

If you’d like to learn more, please check out this recent California CPA Magazine article.

 

Clocking In

 

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

1 Comment

  1. Louis G on July 3, 2019 at 3:12 pm

    I am trying to add days to a date based upon data from another cell. here’s the information and columns relating to the rquest.
    Column “D” is the date of incident (ddmmmyy), Column “E” is the Due to higher date (ddmmmyy), Column “I” is the Classification (either A,B,C,D,E,F) and Column “T” is the “ON DUTY” or “OFF DUTY” column indicating work/off time. In Column “I” the class A,B,C can either be “ON” or “OFF” the clock and if “ON” the clock then it is a 45 day to turn in from the date of incident, if A,B,C is “OFF” the clock, or any other classification is used (i.e. D,E,F) then it is a 15 day turn in to my office for an initial report of the incident. How do I turn this into a calendar date using this information. Here is the formula I have in the column now for Column E” but recently realized it is insuuficient to correctly calculate this information to higher level leadership.
    =IF(I276=”A”,D276+45,IF(I276=”B”,D276+45,IF(I276=”C”,D276+45,IF(OR(I276=”D”,I276=”E”,I276=”F”,I276=”GND OHR”,I276=”AIR OHR”,I276=”UAS OHR”),D276+15,””))))
    As you can see I276=”A” doesn’t correctly reflect the other variable of being either “ON” or “OFF” duty and ONLY adds the 45 day addition to the tu n in timeline. An “OFF” the clock incident will still be a 45 day window from the date of incident but should correctly be (I276=”A”,T276=”ON”,D276+45) +(I276=”A”,T276=”OFF”,D276+15) or something to that formula though this will not work in Excel. Please help if possible, thank you.

Leave a Comment