View original (French)

Conversion and calculation of hours and rates h

Solved
Alain72 -  
 Alain72 -
Bonjour,
1. Under EXCEL, I need to calculate my working hours: I start at 6:30 PM and finish at 2:15 AM the next morning.
2. Then, I need to calculate my salary, for example, 55 Francs per hour for 7 hours and 45 minutes.
How to convert the 45 minutes into hundredths of an hour while keeping the hours.
The reference is 7 hours and 45 minutes, and the result should be 7 hours and 75 hundredths.
Thank you for helping me find the calculation formulas, including the transition from one day to the next and the conversion formula into hundredths of an hour.

I am using EXCEL 2007 on Windows 7.

4 answers

  1.
    Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hi,

    Respect the Excel format, for entering hour values the separator format is generally the colon: unless you have changed your settings.

    Example you have in A1 the start time 18:30
    in A2 the end time 2:15 the formula is
    =MOD(A2-A1;1)

    to convert the result which is 7:45 into decimal format, the formula is cell*24 if your Mod formula is in C2
    =C2*24

    if you want to directly calculate the total

    =55*(C2*24)

    and you set the cell to currency format for example
    but you could also enter the hourly amount in a cell for example in C1 you enter 55.00 the formula becomes

    =C1*(C2*24)
    A+
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    3
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Good evening
    for a "mixed" calculation
    arrival at A1
    departure at B1
    =1-A1+B1 for the result
    if both cases can be mixed:
    =IF(A1>B1;1-A1+B1;B1-A1)
    to get the total:
    use the SUM(Field) function and format the result cell as:
    custom / and in the type window, write:
    [hh]:mm
    with the H in brackets
    Finally, for calculating the amount, it is not necessary to convert to hundredths, the total hours result multiplied by 55 works just as well.
    But you can directly use the formula:
    =Cell*24 which gives you what you want (7:45 becomes 7.75)

    Best regards

    Let’s ask ourselves if we are not alone in understanding what is being explained?
    1
  3. Alain72
     
    Thank you very much for everything. It will be a lot of work, but with all this help, I will manage. I am really very grateful.

    Alain
    0
  4. Alain72
     
    Thank you so much for everything. I just have to implement the proposed solutions.
    With all my gratitude.

    Alain
    0