How can I calculate time in Excel through macros? Ie There is an actual start and the actual end you need to calculate how much time was spent. But it should take into account weekends and holidays and working hours from 9:00 to 18:00.

    2 answers 2

    Here is a very simple macro for calculating the number of working hours on workdays:

    Sub time() Dim StartWork As Date Dim EndWork As Date Dim Msg StartWork = InputBox("Введите дату начала работ в формате дд/мм/гггг") EndWork = InputBox("Введите дату окончания работ в формате дд/мм/гггг") Msg = Application.WorksheetFunction.NetworkDays(StartWork, EndWork) * 9 Msg = Msg & " рабочих часа(ов) в периоде" & vbNewLine Msg = Msg & "с " & StartWork & " по " & EndWork MsgBox Msg End Sub

    The description of the NetworkDays feature is here .

    If it is necessary to take into account the opening and closing hours, it will be a little more complicated.

    All the same (and faster) can be done without using VBA with the usual formulas:

     =ЧИСТРАБДНИ.МЕЖД("22.09.2015";"28.09.2015")*9

    - calculates the number of working days in the range of dates and multiplies by 9 hours;

     =(ЧИСТРАБДНИ.МЕЖД("22.09.2015 12:00:00";"28.09.2015 15:00:00")-2)*9+18-ЧАС("22.09.2015 12:00:00")+18-ЧАС("28.09.2015 15:00:00")

    - calculates the number of working days in the range without the extreme two days, multiplies by 9 hours; and separately calculates the number of hours in the last two days;

    Description of the function ЧИСТРАБДНИ.МЕЖД here

    Quite rude and "in the forehead," without checking the hitting of times in the working period ... But he thinks)

    • In principle, what you need, but here is one thing. This macro should count from the uploaded report how to do this? - Pavel
    • Then, instead of =inputbox(... set =Range("A1") and =Range("A2") ; instead of A1 and A2 substitute two cell addresses with the beginning and end dates of the period. Important note! The active sheet when running the macro should be sheet with these dates, otherwise you will need to register the full path with the name of the book and sheet. - Eugene Oldman
    • Healthy And how from 2 columns to give information in 3 columns? That is, if the macro counted in cell A1 and A2 and gave the result in A3. And is it possible to count not just the cells but the whole column at once? - Pavel
    • Um, this can be done by a large number of ways ... For example, run the executable formula over the cycle (number of date pairs in columns) and write the result to a cell in the third column. But I don’t understand why using VBA for this, if you can just write a formula in the third column ... - Eugene Oldman

    To calculate using macros, you need to refer to the VBA language. It has the function DateDiff for it .

    • Is it possible in more detail? - Pavel
    • if date type cells can be calculated as an ordinary difference, it is not necessary to use macros - Serge Esmanovich