Payment Calculations based on Timestamps in Excel

The user enters the start time-stamp and the end timestamp and the rate per hour or minute and based on this data you can quickly and easily calculate the payment or total amount due using a simple formula.
Time-stampjust means that you enter the date and time in one shot in the relevant Excel worksheet cell. Excel treats the date as an integer and the time as a fraction. You enter the timestamp as ’04/15/2012 9:00′ which means ’15th April 2012 9 am’. Once you’ve entered both the ‘start’ and ‘end’ timestamps you can calculate the total amount or total payment as shown in the video.

Formula entered in cell E4 to calculate amount:

=(C4-B4)*24*D4
C4=End Timestamp
B4=Start Timestamp
24 = total number of hours in a day
D4= hourly rate

If the rate is per minute then the formula would be:

=(C4-B4)*24*60*D4

Watch the Excel training video below to learn how to use timestamps in Microsoft Excel to calculate the payments:

3 thoughts on “Payment Calculations based on Timestamps in Excel

  1. Lizette

    Hi, I wonder if you can help me with a calculating dilema that I have in my worksheet. Here’s the issue. You get paid according to your minutes in a call. the standard rate is ($.24), while and over ride rate is paid at ($.35). I created a formula (rather long) that calculates ok if the calls are before and after the cut off time. if a call starts at 8:30:00 and ends at 9:25:00, the formula does not works and it returns a large #. I hope you can help me fix this issue. Thanking you in advance for your assistance.

    =IFERROR(IF(AND(TIME(HOUR($M28),MINUTE($M28),SECOND($M28))<TIME(HOUR($Z$23),MINUTE($Z$23),SECOND($Z$23))),($M28-$L28)*$AA$17*1440,IF(AND(TIME(HOUR($M28),MINUTE($M28),SECOND($M28))),($M28-$L28)*$AA$16*1440,IF(AND(TIME(HOUR($L28),MINUTE($L28),SECOND($L28))TIME(HOUR($Z$23),MINUTE($Z$23),SECOND($Z$23))),(($Z$23-$L28)*$AA$17*1440)+(($M28-$Z$23)*$AA$16*1440)))),” “)

    Z23= TIME CUT OFF (9:00:01)
    L28=START.TIME (STAMPED)
    M28=END.TIME (STAMP)
    AA16=REGULAR RATE OF $.24
    AA17=OVERRIDE RATE OF $.35

    Reply
    1. Lizette

      Dinesh, I would like to clarify (Incase I failed to) that with the above post am trying to perform two time calculations:
      ((TIME=9:00:01)*.24*1440) = TOTAL PAYMENT OF THIS PARTICULAR CALL.

      Reply
  2. Lizette

    SORRY, but it cut the first part of the calculation: (TIME<=9:00:00)*.40*1440) PLUS the above calculation. Thanks.

    Reply

Leave a Reply

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