Calculate hours

[Aussiebear]

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]

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]

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]

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]

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]

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]

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
Chg All.jpg
select CHARGEABLE only
Chg Chgble.jpg
select non chargeable only
Chg NonChgble.jpg

[snb]

@yongle

Nice idea !

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

[Yongle]

@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]

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]

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]

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]

@aussiebear

Why do you neglect the suggestions that have been proposed ?

[SamT]

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]

@aussiebear

Why do you neglect the suggestions that have been proposed ?

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

[Aussiebear]

@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]

=If(And(or(A3=A2,A3=""),F2="Y"),1440*C3-C2,"")

[Yongle]

@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]

@ 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]

Subtracting time against time gives some surprising results. None more so than when i subtract time that has a N for non billable.