timecard

**sanjay07** · 11-26-2009, 11:49 AM

Is there an option where in on entering value in cell D8 say any thing above 8 hrs the extra minutes would be put in the next cell E8

eg: I put 8.15 in cell D8 on hiting enter the .15 goes to E8 and only 8 remains in D8

No macro plz

**DonkeyOte** · 11-26-2009, 12:19 PM

You would need to use VBA if you wish to amend D8 value upon entry (where required).

If you wish to avoid VBA...

You could set D8 to be whatever was input - eg 08:15, 04:30 etc... then normalise D8 to Max of 8 hours in E8, ie:

E8: =MIN("08:00",SUM(D8))

and subsequently apportion remainder to F8 (your E8 originally)

F8: =E8-D8

**teylyn** · 11-26-2009, 04:17 PM

Maybe it will be sufficient to format the data entry cell to just display the hour? Format the cell with custom format

h

then in the next cell use

=MINUTE(A1)

**darkyam** · 11-26-2009, 04:23 PM

Teylyn's approach is efficient in that it requires no helper columns. Bear in mind if you use it, though, that the underlying value of D8 will still be 8.15. You'll have to subtract E8 for any math you want to do on it or otherwise round down to the nearest hour.

**sanjay07** · 11-30-2009, 08:33 AM

none of the soln seem to work

**darkyam** · 11-30-2009, 08:44 AM

Try this workbook. The first solution is a modification of Teylyn's approach. The next two are DonkeyOte's in action, one with numbers and the other with time values.

**sanjay07** · 11-30-2009, 09:42 AM

the first soln was helpful, but i need this condn only for beyond 8 hrs

so say if it is 7.15 minutes then there is no transfer of the .15 mins in the next row

**DonkeyOte** · 11-30-2009, 09:46 AM

sanjay, I would suggest reading through all posts in full.

You can pull remainder using

=MAX(0,A1-"08:00")

However the above does not in any way shape or form alter the original input - ie if input is 8:15 it remains 8:15 with :15 in B1 which you imply is a requirement:

Originally Posted by sanjay

eg: I put 8.15 in cell D8 on hiting enter the .15 goes to E8 and only 8 remains in D8

if you want to normalise initial entry to a max of 8 hours you are left with two alternatives, either

a) use VBA

b) use 2 formulas as previously outlined - one which caps the 8 and the other which accounts for excess should it exist.

else you will need to account for the excess time as stored in A values in any subsequent calculations you conduct.