PDA

View Full Version : Calculate hours



Aussiebear
05-20-2015, 04:02 AM
I am seeking a method of calculating the time that is billed to a client. That is only time spent on the job, not including time spent prestarting machine, crib, or servicing machine, since they are operating costs incurred by me. Any ideas here?






Time
Reason


6.30am
Prestart Machine


6:35am
Clearing & Stacking Timber


10:38am
Crib


11:05am
Clearing & Stacking Timber


2:30pm
Crib


2:50PM
Clearing & Stacking Timber


5:45PM
Service Machine


6:12PM
End of Day

Aussiebear
05-20-2015, 04:27 AM
Obviously the day has 3 operating periods. Ist period 6:35 to 10:38 (4:03), 2nd period 11:05am to 2:30pm (3:35), 3rd period 2:50pm to 5:45pm (2:55) and if my maths are correct thats 10:23 of client billing time, 1:18 of owner operational costs in a total 11:42 day

mancubus
05-20-2015, 04:33 AM
assuming times are entered in proper time format (like 6:35 AM or 5:45 PM) in column A and reasons in B, formula in C2 (copied down)


=IF(OR(B2="Clearing & Stacking Timber",B2="Other Chargeable Reason"),A3-A2,0)
or

=IF(AND(B2<>"Prestart Machine",B2<>"Crib",B2<>"Service Machine"),A3-A2,0)

if the number of reasons not chargeable is more than the chargeable, i recommend you use formula 1.

other methods can be found as well.


you can calculate the time not chargeable in column D like:

=IF(OR(B2="Prestart Machine",B2="Crib",B2="Service Machine"),A3-A2,0)

snb
05-20-2015, 04:53 AM
Sub M_snb()
msgbox [text(sum(if(left(B1:B8,2)="Cl",offset(A1:A8,1,0)-A1:A8,0)),"hh:mm")]
End Sub

Aussiebear
05-20-2015, 05:37 AM
Having had yet another think about this.... ( you can stop rolling about on the ground in fits of laughter....) I might change the concept to to simplify the calculations given that the task could change either during the day or during the week and I don't want to have to rewrite the formulas over & over again.





Time
Reason


6:30am
Prestart machine


6.35am
Ready


10:38am
Crib


11:05am
Ready


2.30pm
Crib


5.45pm
Service machine


6:12pm
End of Day

SamT
05-20-2015, 06:51 AM
Time

Billable?
Reason


6.30am


Prestart Machine


6:35am
x
Clearing & Stacking Timber



10:38am

Crib


11:05am
rough terrain

Clearing & Stacking Timber



2:00pm

f

Stroke
Client's ego



2:30pm


Crib


2:50PM
yes
Clearing & Stacking Timber


5:45PM

Service Machine


6:12PM

End of Day



BTDT as a contractor me self. This way handle multiple tasks of same type

With VBA, Billable time runs from Cell <> "" to Cell = ""

Non-Billable from = ''" to <> ""

This way, you can use Key codes in billable column to track other Business Information.

See my PM to you :)

Yongle
05-20-2015, 07:36 AM
Another option

1 Create a table of valid tasks and whether chargeable or not
2 Create a simple user form to input start and finish times for each task (start time could be end of previous task unless overwritten). Each task to be chosen from dropdown of available tasks (per 1 above)
3 Data captured by userform is written to a simple table
4 Use table features to select chargeable /non-chargeable or any combination permitted
4 Could even add a column for Client Name (or number) and use data to generate invoice

Workbook attached (did not create userform). Uses vlookup to look-up up valid tasks to determine whether chargeable or not.

ALL HOURS
13448
select CHARGEABLE only
13449
select non chargeable only
13450

snb
05-20-2015, 07:52 AM
@yongle

Nice idea !

You only need less columns (easier to fill the table).

Yongle
05-20-2015, 08:15 AM
@snb

Agreed.
But coming from a background where I had to find somewhere to dump all my time (where non-chargeable time was strenuously "discouraged" by my bosses), I would still personally keep a tab on all hours. Allows calc of chargeable/non-chargeable ratio etc (unless @Aussiebear does not want to know how much of his time is non-chargeable.)
Old habits die hard, I guess!

Aussiebear
05-21-2015, 03:23 AM
I very much do need to know how much of my day is non chargeable. if I'm working 12 hours days, I need to find away to make more hours as income earning as possible. One of the mere principles of private enterprise.

Up until now I had planned to use a form with various buttons, to time stamp what happens through the working day. Log On, Ready Up, Crib, Breakdown, Relocating, Log off etc. Now this has been easy to do, but I ran into a problem with selecting the task for the day. Soon to launch a new thread on that one.

Because I'm using Mac 2001 Excel, I can't construct a number of envisaged functions ( no Active X) but I'd love to fill part of my screen with an active clock (Digital if possible). Attached is the workbook showing some data and the forms I had been working towards. Perhaps someone may like to see where I was heading and comment.

Paul_Hossler
05-21-2015, 01:47 PM
Don't know the Mac, but have you thought about using Access or something similar? Either by itself or in conjunction with Excel?

Access to do the Client and Job tracking, and Excel to do the estimating


It seems like there's a lot of un-normalized data and you have to enter a lot of information more than once


You could have a series of Userforms and store data on a worksheet: you only see the UF, not the WS

Example: UF_ClientMaint with Add, Update, Delete buttons to retrieve or store data

I see linked data tables for Clients, Jobs, Required Equipment, etc.

Paul_Hossler
05-21-2015, 01:51 PM
Time


Billable?

Reason



6.30am


Prestart Machine



6:35am

x

Clearing & Stacking Timber



10:38am


Crib



11:05am

rough terrain

Clearing & Stacking Timber



2:00pm

f

Stroke
Client's ego



2:30pm


Crib



2:50PM

yes

Clearing & Stacking Timber



5:45PM


Service Machine



6:12PM


End of Day




BTDT as a contractor me self. This way handle multiple tasks of same type

With VBA, Billable time runs from Cell <> "" to Cell = ""

Non-Billable from = ''" to <> ""

This way, you can use Key codes in billable column to track other Business Information.

See my PM to you :)


Your 2:00 task takes 30 minutes -- WOW!!!

snb
05-21-2015, 02:04 PM
@aussiebear

Why do you neglect the suggestions that have been proposed ?

SamT
05-21-2015, 02:14 PM
Aussie Bear,

I got the Digital Clock Form to work and I thought that If this is always on, it would make a minor Activity menu. I have a couple of LED dtyle TTF Fonts if you want to use them

The form frmDigitalClock has no code except to run the clock. The actual Clock code is in mod_ClockTimer.

I decided to update the clock on the next whole minute thinking that you woulld not be all that keen on seconds and to save 59 events/minute.

Option Explicit

'Module Variables
Dim mNextMinute

Sub ClockTime()
Dim nextMinute As Date

With frmDigitalTimeClock
.lblClock.Caption = Format(Now, "hh:mm")
.lblDay.Caption = WeekdayName(Weekday(Now))
.lblDate.Caption = Format(Now, "M-DD-YY")
End With

mNextMinute = DateSerial(Year(Now), Month(Now), Day(Now)) + _
TimeSerial(Hour(Now), Minute(Now) + 1, Second(0))

Application.OnTime _
Procedure:="ClockTime", _
EarliestTime:=mNextMinute
End Sub

Sub StopTime()
'Prevent updating time. I run when Clock stops being visible

Application.OnTime EarliestTime:=mNextMinute, _
Procedure:="ClockTime", Schedule:=False
End Sub

Aussiebear
05-21-2015, 02:53 PM
@aussiebear

Why do you neglect the suggestions that have been proposed ?

Nothing has been rejected, I'm musing through all concepts

Aussiebear
05-21-2015, 11:54 PM
@snb, The problem here is that if I used your formula as it stands I would need to rewrite it every time I changed the method of operation. To overcome this, I combined part of Yongle's concept ( putting a Y or N value in another column ). Using two criteria as you had suggested my formula now reads:
=If(And(A3=A2,F2="Y"),1440*C3-C2,""). This currently works for all but the last line.

@Yongle, "Sleeping on the job" and "135 mins for Crib" Grrrr! Bet you thought I wouldn't notice those ones. I think we need to have a discussion about your future with my company. ( Please make sure your desk is cleaned out before the meeting)

@Paul, What can I say, I agree that in the end I will try to use Access as the back end, but for now I'm in "suck it and see" mode.

snb
05-22-2015, 01:02 AM
=If(And(or(A3=A2,A3=""),F2="Y"),1440*C3-C2,"")

Yongle
05-22-2015, 01:02 AM
@aussiebear - glad to learn that you were not "sleeping on the job" and spotted my little teasers.
BTW very few members would understand the word "crib" - which was likely taken down-under by the Cornish miners. But, given your mining connection, 'tis no surprise that you use this word.

Aussiebear
05-22-2015, 02:32 AM
@ snb, righto will try this one.

@Yongle, yes I agree, but some thing transverse the world over time. I didn't know the word in this sense before joining the mining industry in 2008

Aussiebear
05-22-2015, 02:40 AM
Subtracting time against time gives some surprising results. None more so than when i subtract time that has a N for non billable.

Paul_Hossler
05-22-2015, 06:38 AM
AussieBear -- I figured out the Clock part, but now I'm stuck on how to embedded it. Superglue maybe?? :devil2:

13469

p45cal
05-22-2015, 07:22 AM
Making Log on and Log off both billable (as they're both part of Clearing & Stacking Timber) would make you more money wouldn't it?

Yongle
05-22-2015, 08:29 AM
Thoughts on chargeable time:

As an employer I would expect to pay my staff whenever they were busy. The same would apply if my staff were self-employed contractors (your situation). Or I might agree a rate for the whole job (if that can be accurately determined in advance - which I think is not the case here)

As a contractor I would expect to charge for every hour I am busy specifically on the customer's business
If the machine needs setting up, and servicing on site every day, then that time is chargeable.
So other than "crib" time and "sleeping on the job" everything else should be chargeable and the only question is at what rate?

I would charge at 2 different rates:
one rate for the time the machine is running and
a different lower rate for set-up and servicing

As an employer (employing a contractor) I would expect that approach too.

SamT
05-22-2015, 09:10 AM
Jerrald Hayes is a great businessman, even if we don't get along at all. Politics, dontcha know :(



Anyway he developed a great spreadsheet for figuring out what you need to charge as a contractor. This is the last of his free, distributable versions.

IMHO, it is well worth playing around with.

SamT
05-22-2015, 10:36 AM
Aussie,

This time sheet has a working time calculator.


Option Explicit

Sub WorkTimeCalc()
Dim TimeCol As Range
Dim BillableCol As Range
Dim NonBillableHoursCol As Range
Dim BillableHoursCol As Range
Dim TaskTime As Date

Dim Cel As Range

Dim LastRowA As String
LastRowA = CStr(Cells(Rows.Count, 1).End(xlUp).Row)

Set TimeCol = Range("A2:A" & LastRowA)
Set BillableCol = Range("B2:B" & LastRowA)
Set NonBillableHoursCol = Range("D2:D" & LastRowA)
Set BillableHoursCol = Range("E2:E" & LastRowA)


For Each Cel In TimeCol
If Cel.Offset(1) = "" Then Exit For
If Cel.Offset(0, 1) = "" Then 'CDate Functions left over from development
NonBillableHoursCol.Rows(Cel.Row - 1) = CDate(Cel.Offset(1) - Cel)
Else: BillableHoursCol.Rows(Cel.Row - 1) = CDate(Cel.Offset(1) - Cel)
End If
Next


NonBillableHoursCol.Rows(Cel.Row).Offset(1) _
= WorksheetFunction.Sum(NonBillableHoursCol)
BillableHoursCol.Rows(Cel.Row).Offset(1) _
= WorksheetFunction.Sum(BillableHoursCol)
Cel.Offset(2, 2) = "Totals"


End Sub