CODEWITHSUNDEEP
excelexcel-formulavba
changer
changer

Reputation: 409

How to count hours in excel

I have xls file in following format

Name    1              2             3            4
John    09:00-21:00                  09:00-21:00
Amy                    21:00-09:00                09:00-21:00

Where 1,2,3,4 and so on represent days of current month, 09:00-21:00 - working hours.

I want to calculate salary based on the following conditions:

09:00-21:00 - 10$/hour
21:00-00:00 - 15$/hour
00:00-03:00 - 20$/hour
etc.

and so on (every hour can have it's own cost, for example 03:00-04:00 - 20$/hour, 04:00-05:00 - 19$/hour, etc.)

How can i accomplish this using only Excel (functions or VBA)?

P.S. Easy way: export to csv and process in python/php/etc.

Upvotes: 2

Views: 406

Answers (3)

Dave Sexton
Dave Sexton

Reputation: 11188

I have a method that uses nothing but formulas. First create a lookup table which contains every hour and rate in say columns K & L, something like this:

K       L
08:00   15
09:00   10
10:00   10
11:00   10
12:00   10
13:00   10
14:00   10
15:00   10
16:00   10
17:00   10
18:00   10
19:00   10
20:00   10
21:00   15
22:00   15
23:00   15

Make sure you enter the hours as text by entering a single quote before the digits.

Then if your hours were in cell B2 you could then use this formula to calculate the total: =SUM(INDIRECT("L"&MATCH(LEFT(B2,5),K2:K40,0)&":L"&MATCH(RIGHT(B2,5),K2:K40,0)))

All the formula is doing is getting the left and right text of your work time, using MATCH to find their positions in the lookup table which is used to create a range address which is then passed to SUM via the INDIRECT function.

If you need to worry about minutes all you need to do is create a bigger lookup table which holds every minute of the day. You may need to add some extra logic if your work days span midnight.

Upvotes: 1

Scott Holtzman
Scott Holtzman

Reputation: 27239

Here is a non-VBA solution. It's a pretty nasty formula, but it works. I am sure it could be made even easier to use and understand with some more ingenuity:

Assuming the spreadsheet is set up like this:

Data Grid

Enter this formula in cell G1 and drag down for your data set:

=IF(ISBLANK(B2),"",IF(LEFT(B2,2)<MID(B2,FIND("-",B2)+1,2),SUMIFS($P$2:$P$24,$Q$2:$Q$24,">="&LEFT(B2,2),$Q$2:$Q$24,"<="&MID(B2,FIND("-",B2)+1,2)),SUMIF($Q$2:$Q$24,"<="&MID(B2,FIND("-",B2)+1,2),$P$2:$P$24)+SUMIF($Q$2:$Q$24,">="&LEFT(B2,2),$P$2:$P$24)))

To explain the formula in detail:

  1. IF(ISBLANK(B2),"" will return a empty string if there is no time for a given person / day combination.
  2. LEFT(B2,2) extracts the start-time into an hour.
  3. Mid(B2,Find("-",B2)+1,2) extracts the end-time into an hour.
  4. IF(LEFT(B2,2)<MID(B2,FIND("-",B2)+1,2) will check if the start-time is less than the end-time (meaning no over-night work). If the start-time is less than the end-time, it will use this formula to calculate the total cost per hour: SUMIFS($P$2:$P$24,$Q$2:$Q$24,">="&LEFT(B3,2),$Q$2:$Q$24,"<="&MID(B3,FIND("-",B3)+1,2))
  5. If the start-time is higher than the end-time (meaning overnight work), it will use this formula to calculate: SUMIF($Q$2:$Q$24,"<="&MID(B3,FIND("-",B3)+1,2),$P$2:$P$24)+SUMIF($Q$2:$Q$24,">="&LEFT(B3,2),$P$2:$P$24)
  6. The use of the Find("-",[cell]) splits the start-and- end times into values excel can use to do math against the Time / Cost table.
  7. The formula in column Q of the Time / Cost table is =VALUE(MID(O2,FIND("-",O2)+1,2)) and turns the ending hour to consider the cost into a value Excel can use to add, instead of having the text from your original source format.

Upvotes: 1

CodeJockey
CodeJockey

Reputation: 1981

Do this in VBA! It is native to excel and is easy to learn. Functionally, I would loop through the table, write a function to calculate the dollars earned based on the info given. If you want your results to be live updating (like a formula in excel) you can write a user defined function. A helpful function might be an HoursIntersect function, as below:

Public Function HoursIntersect(Period1Start As Date, Period1End As Date, _
                               Period2Start As Date, Period2End As Date) _
                               As Double
Dim result As Double

' Check if the ends are greater than the starts.  If they are, assume we are rolling over to
' a new day
If Period1End < Period1Start Then Period1End = Period1End + 1
If Period2End < Period2Start Then Period2End = Period2End + 1

With WorksheetFunction
result = .Min(Period1End, Period2End) - .Max(Period1Start, Period2Start)
HoursIntersect = .Max(result, 0) * 24
End With

End Function

Then you can determine the start and end time by splitting the value on the "-" character. Then multiply each payment schedule by the hours worked within that time:

DollarsEarned = DollarsEarned + 20 * HoursIntersect(StartTime, EndTime, #00:00:00#, #03:00:00#)
DollarsEarned = DollarsEarned + 10 * HoursIntersect(StartTime, EndTime, #09:00:00#, #21:00:00#)
DollarsEarned = DollarsEarned + 15 * HoursIntersect(StartTime, EndTime, #21:00:00#, #00:00:00#)

Upvotes: 1

Related Questions