How to take a date and time and split the minutes between the two across the hours

[derek12345]

OK the title is a tad long, here is what I need:

Say you have a login time and a logout time, this will give you a specific duration in hours and minutes. I would like to spread the minutes across the hours for the day in question:

Example:

Login Time Logout Time Total Duration
02/02/2014 11:55 02/02/2014 18:32 6:37:03

8-9am 9-10am 10-11am 11-12pm 12-1pm 1-2pm 2-3pm 3-4pm 4-5pm 5-6pm 6-7pm ETC
0 0 0 5 60 60 60 60 60 60 32


Is this even possible? I have checked every formula and decided VBA was the only way, but have little to no experience.

 

[WaterGypsy]

Assume your headings are in row 1, Logon col A, Log Out B (only use the time not date), Duration C, only put start time in d through to r (or wherever) as 08:00 , 09:00 , 10:00 etc.

then try this formula in D, and copy across to column before the last date:

=IF($A2>E1,0,IF($A2>D1,60-VALUE(MINUTE($A2)),60))-IF($B2<=D1,60,IF($B2>=E1,0,60-VALUE(MINUTE($B2))))

 

[derek12345]

Assume your headings are in row 1, Logon col A, Log Out B (only use the time not date), Duration C, only put start time in d through to r (or wherever) as 08:00 , 09:00 , 10:00 etc.

then try this formula in D, and copy across to column before the last date:

=IF($A2>E1,0,IF($A2>D1,60-VALUE(MINUTE($A2)),60))-IF($B2<=D1,60,IF($B2>=E1,0,60-VALUE(MINUTE($B2))))

Worked like a charm! Thanks for that!