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

 

Get a quick email notice when a new Excel article is available

  • This field is for validation purposes and should be left unchanged.

This article was written by Jeff Lenning

1 comment:

  1. Louis G
    Reply

    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 Reply

Your email address will not be published. Required fields are marked *

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.