Calculating Work Time

J

janiv

Hi,
I have the following hours logged:

03/06/2012 08:38
03/06/2012 23:54
04/06/2012 09:11
04/06/2012 18:50
05/06/2012 08:50
05/06/2012 15:01
06/06/2012 08:42
06/06/2012 19:07
07/06/2012 08:54
07/06/2012 19:08
08/06/2012 19:04
10/06/2012 09:12
10/06/2012 18:33
10/06/2012 21:09
10/06/2012 23:44


How do I calculate how many hours I worked every day?
Something like:
03/06/2012 10:05:00
04/06/2012 08:45:00
10/06/2012 12:46:00

Thanks,
Janiv
 
G

GS

janiv was thinking very hard :
Hi,
I have the following hours logged:

03/06/2012 08:38
03/06/2012 23:54
04/06/2012 09:11
04/06/2012 18:50
05/06/2012 08:50
05/06/2012 15:01
06/06/2012 08:42
06/06/2012 19:07
07/06/2012 08:54
07/06/2012 19:08
08/06/2012 19:04
10/06/2012 09:12
10/06/2012 18:33
10/06/2012 21:09
10/06/2012 23:44


How do I calculate how many hours I worked every day?
Something like:
03/06/2012 10:05:00
04/06/2012 08:45:00
10/06/2012 12:46:00

Thanks,
Janiv.
Click to expand...

Stop time minus start time, perhaps! Calc this for each day (or
start/stop period) and SUM() to total.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
J

janiv

Thanks,
How do I do it in Excel?

Thanks again,


'GS[_2_ said:
;1602620']janiv was thinking very hard :-
Hi,
I have the following hours logged:

03/06/2012 08:38
03/06/2012 23:54
04/06/2012 09:11
04/06/2012 18:50
05/06/2012 08:50
05/06/2012 15:01
06/06/2012 08:42
06/06/2012 19:07
07/06/2012 08:54
07/06/2012 19:08
08/06/2012 19:04
10/06/2012 09:12
10/06/2012 18:33
10/06/2012 21:09
10/06/2012 23:44


How do I calculate how many hours I worked every day?
Something like:
03/06/2012 10:05:00
04/06/2012 08:45:00
10/06/2012 12:46:00

Thanks,
Janiv.-
Click to expand...

Stop time minus start time, perhaps! Calc this for each day (or
start/stop period) and SUM() to total.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussio
Click to expand...
 
G

GS

Enter the dates of StartTime in column 'A';
Enter the StartTime in column 'B';
Enter the StopTime in column 'C';
Enter the following formula in column 'D'...

=IF(AND($B2<>"",$C2<>""),ROUND(MOD($C2-$B2,1)*24,2),"")

...where row 1 has headings and your time records start in row 2.

The formula returns time in hundredths of an hour, and accounts for
starts before midnight that include stops after midnight. If either
StartTime or StopTime is empty the result is blank until both fields
have valid times entered. So...

Start=1:00 PM; Stop=5:30 PM; ElapsedTime=4.5 hours
Start=9:00 PM; Stop=1:30 AM; ElapsedTime=4.5 hours

Copy the formula down.
It's okay to have multiple start/stop sessions for the same date.
Use the AutoSum feature to total how you want.

You can enter start/stop times 'on-the-fly' using the keyboard combo
'Ctrl+Shift+;' if you work multiple projects in any workday.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
J

janiv

Thanks,
I can't put start time and end time in different columns.
I have all of them on the same column, and another column that is set t
1 if it is entrance or 0 if it is exit.

Anyt other idea?

Thanks,

'GS[_2_ said:
;1602662']Enter the dates of StartTime in column 'A';
Enter the StartTime in column 'B';
Enter the StopTime in column 'C';
Enter the following formula in column 'D'...

=IF(AND($B2<>"",$C2<>""),ROUND(MOD($C2-$B2,1)*24,2),"")

...where row 1 has headings and your time records start in row 2.

The formula returns time in hundredths of an hour, and accounts for
starts before midnight that include stops after midnight. If either
StartTime or StopTime is empty the result is blank until both fields
have valid times entered. So...

Start=1:00 PM; Stop=5:30 PM; ElapsedTime=4.5 hours
Start=9:00 PM; Stop=1:30 AM; ElapsedTime=4.5 hours

Copy the formula down.
It's okay to have multiple start/stop sessions for the same date.
Use the AutoSum feature to total how you want.

You can enter start/stop times 'on-the-fly' using the keyboard combo
'Ctrl+Shift+;' if you work multiple projects in any workday.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussio
Click to expand...
 
G

GS

janiv expressed precisely :
Thanks,
I can't put start time and end time in different columns.
I have all of them on the same column, and another column that is set to
1 if it is entrance or 0 if it is exit.

Anyt other idea?
Click to expand...
You can modify the formula to suit your layout. Here's the layout I
use...

Date - Project/Job - Service - StartTime - StopTime - ElapsedTime

...because it's easier to manage by Project/Job and Date. Personally, I
can't fathom why you'd use separate rows for start/stop times. (No
offense.., just seems like a poor design to me!)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
J

janiv

Thanks,
As I get the table from a 3rd party DB, I cannot modify its layout.
Is it another way?

Thanks,

'GS[_2_ said:
;1602823']janiv expressed precisely :-
Thanks,
I can't put start time and end time in different columns.
I have all of them on the same column, and another column that is se to
1 if it is entrance or 0 if it is exit.

Anyt other idea?
-
Click to expand...
You can modify the formula to suit your layout. Here's the layout I
use...

Date - Project/Job - Service - StartTime - StopTime - ElapsedTime

...because it's easier to manage by Project/Job and Date. Personally,

can't fathom why you'd use separate rows for start/stop times. (No
offense.., just seems like a poor design to me!)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussio
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Reverse Index Match 2
Calculating interest due on late loan payments 1
Check if a date comes before/after a range of dates 0
Find then highlight in yellow 6
Get text from last and first cell in a column 3
How do I count the number of blank cells 0
Max-Min with specific criteria 1
15 minute data to daily data 1

Top