Clock calculates wasted time at meetings

[PhilipCCFE]

On Boingboing.net today there was a Clock that calculates wasted time at meetings -- http://feeds.boingboing.net/~r/boingboing/iBag/~3/wa8scbRULsg/clock-calculates-was.html


I had the thought of implementing something like that using excel.

Cost of Meeting				


=NOW()-TIME(LEFT($F$7,2),RIGHT($F$7,2),0)				


100	x	10	x	12.40

Hourly Rate		People		Time start

Where F7 is the time (as entered at the start of the meeting).


however B$ the (=NOW[..]) cell returns a number I am not sure how to work with; its current value is 40317.05477


The requirement would be to have a decimal value for time, then multiply that by Hourly Rate, and Number of People, to resolve the current cost of the meeting

 

[Hui]

Philip

If you enter the start time in

F7: 10:30 am

and in F8: =Now()

in F9: =(F8-INT(F8))-F7

Then in F10 you can put the cost F10: =F9*100*10

Format F7:F8 as h:mm AM/PM

Format F9 as h:mm

Format F9 as $ ###,###.##

and press the F9 Key at the end of the meeting


Dates and Times are stored in Excel as Numbers where the Date is the Integer (part before the decimal) and the Time is the Decimal part (after the decimal place)

In our example entering the Start Time F7 as 10:30am Excel stores that as a 0.4375 where 0.4375 is 10:30 divided by 24 as a decimal.


The End of the meeting F8 is =Now() which excel will store as a Date.Time so it is likely to be something like 40,317.91, where 40317 is the date 19 May and the time is 0.91*24 = 9:50 pm


So to get the elapsed time you need to take the Start Time away from the Finish Time, but must remove the date part first =(F8-INT(F8))-F7 does the trick

what is left is a Decimal Time eg: 0.23 which is 0.23 x 24 Hrs


So you can format that as h:mm and it will show the elapsed time in Hrs:Min but you can also multiply it directly by your Cost per Hr and No. of People to get the cost.


This won't work for meetings that start in different days, but you probably don't want to know the cost of that anyway...

 

[Squiggler47]

=(mod(f8,1)-f7) is a better way as it removes one of the references to F8


=(mod(now(),1)-f7)*24 will give the decimal time which can then be multiplied by the hourly rate!

 

[PhilipCCFE]

Thanks Squiggler


That now works much better