Subtract Time From Total Hours

[Alan_P]

Hi Guys,

I'm trying to create what I (very mistakenly) thought would be a simple TimeSheet for my office... I basically have the below 3 variables:

ClockIn : 07:45 [Typed by user]
Current Time: 14:45:00 [using "=Now()"]
Hrs Worked: 07:00 [Simple subtraction of Current - ClockIn]

Now I want to take the Hrs Worked away from a weekly total of 35 hours and I just can't seem to get it to work! I've been googling like a madman and I just seem to be going round in circles so any help would be very much appreciated!!

I'm trying to do this in VBA but at this point I would gladly accept a formula, but ideally I would like to not have to format the cells (much)

Many Thanks,
Alan.

 

[edmitchell]

Alan Hi,

do you want the answer back as HH:MM or as a decimal (e.g. 32:43 (with seconds as well?) or 32.72, both of which would be 32 hours and 43 minutes)?


Ed

 

[Alan_P]

Hi Ed,

Thanks for taking the time to have a look!

Preferably I would like it to be HH:MM:SS

Thanks,
Alan.

 

[edmitchell]

Alan

assuming the 'Hrs remaining' is in cell B8 (e.g. 35 - 'Hours worked') then the formula you need to return the result as a string is

=TEXT(INT(B8),"00:") & TEXT(INT(MOD(B8,1)*60),"00:") & TEXT(INT((MOD(B8,1)-B11/60)*3600),"00")

(seems to work having run a few examples through it!)


Ed

 

[FormR]

Now I want to take the Hrs Worked away from a weekly total of 35 hours

Hi, something like this? Custom format the cell that contains the formula as [hh]:mm

Excel 2013/2016
	A	B
1	Clock In	07:45
2	Current Time	10:32
3	Hrs Worked	02:47
4	Hrs Remaining	32:12
Sheet1

Cell Formulas
Range	Formula
B4	="35:00"-B3

 

[Alan_P]

Can't believe it was that simple!! Think I'd gone excel blind!

Thanks for the help guys, much appreciated!!