#1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 16
Default Total Hours

Hi Everyone,

Using Excel 2003. I am calculating my hours worked. I enter in the start
date and time in the B column and the end date and time in the C column. I
use the shortcut Ctrl : <space Ctrl+Shft+ : to enter this. I have =
TEXT(C3-B3,"h:mm") This is working fine.

At the bottom of my D column I have this =SUM(D2:D16) but it is returning
00:00:00.

I tried using the format cell/custom and picked the hours and minutes as
well as other formats but I can't get it to show up.

I need the total hours and minutes in cell D17

Thanks,
Linda





  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,533
Default Total Hours

Hi Linda

You can not sum on 'Text' cells

To calculate hours worked use:

=C3-B3

and format column D as custom [t]:mm

Hopes this helps.
....
Per

"Linda RQ" skrev i meddelelsen
...
Hi Everyone,

Using Excel 2003. I am calculating my hours worked. I enter in the start
date and time in the B column and the end date and time in the C column.
I use the shortcut Ctrl : <space Ctrl+Shft+ : to enter this. I have =
TEXT(C3-B3,"h:mm") This is working fine.

At the bottom of my D column I have this =SUM(D2:D16) but it is returning
00:00:00.

I tried using the format cell/custom and picked the hours and minutes as
well as other formats but I can't get it to show up.

I need the total hours and minutes in cell D17

Thanks,
Linda






  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 16
Default Total Hours

I wondered about the text but I didn't know how to get it to give me total
hours. I tried yours and My start time is 12/20/2009 9:43 and my end time
is 12/20/2009 10:43. The total hours for the row is showing 0:00:00. I
typed in -c3-b3 then clicked on the format menu and selected custom but
there wasn't a [t]:mm in there so I selected [t]:mm:ss. When I look in the
formula bar, there is no format set so I tried typing it in but I keep
getting a calculation error, I don't exactly know what needs " " or ( ) or
[ ]

Linda




"Per Jessen" wrote in message
...
Hi Linda

You can not sum on 'Text' cells

To calculate hours worked use:

=C3-B3

and format column D as custom [t]:mm

Hopes this helps.
...
Per

"Linda RQ" skrev i meddelelsen
...
Hi Everyone,

Using Excel 2003. I am calculating my hours worked. I enter in the
start date and time in the B column and the end date and time in the C
column. I use the shortcut Ctrl : <space Ctrl+Shft+ : to enter this. I
have = TEXT(C3-B3,"h:mm") This is working fine.

At the bottom of my D column I have this =SUM(D2:D16) but it is returning
00:00:00.

I tried using the format cell/custom and picked the hours and minutes as
well as other formats but I can't get it to show up.

I need the total hours and minutes in cell D17

Thanks,
Linda








  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 793
Default Total Hours

To see the difference, in hours (in decimal format), in Col D simply enter
this formula in D1 and copy down
=(C1-B1)*24

Then you can add up Col D to get total hours.

"Linda RQ" wrote:

Hi Everyone,

Using Excel 2003. I am calculating my hours worked. I enter in the start
date and time in the B column and the end date and time in the C column. I
use the shortcut Ctrl : <space Ctrl+Shft+ : to enter this. I have =
TEXT(C3-B3,"h:mm") This is working fine.

At the bottom of my D column I have this =SUM(D2:D16) but it is returning
00:00:00.

I tried using the format cell/custom and picked the hours and minutes as
well as other formats but I can't get it to show up.

I need the total hours and minutes in cell D17

Thanks,
Linda





.

  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 793
Default Total Hours

Linda,

Per Jessen's solution is better... but the format given did not work for me.

I tried with [hh]:mm which gave me the required solution.

"Per Jessen" wrote:

Hi Linda

You can not sum on 'Text' cells

To calculate hours worked use:

=C3-B3

and format column D as custom [t]:mm

Hopes this helps.
....
Per

"Linda RQ" skrev i meddelelsen
...
Hi Everyone,

Using Excel 2003. I am calculating my hours worked. I enter in the start
date and time in the B column and the end date and time in the C column.
I use the shortcut Ctrl : <space Ctrl+Shft+ : to enter this. I have =
TEXT(C3-B3,"h:mm") This is working fine.

At the bottom of my D column I have this =SUM(D2:D16) but it is returning
00:00:00.

I tried using the format cell/custom and picked the hours and minutes as
well as other formats but I can't get it to show up.

I need the total hours and minutes in cell D17

Thanks,
Linda






.



  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 4,393
Default Total Hours

Per is from Denmark where the word for hours must begin with a t
If you are working in English use [h]:mm
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP


"Linda RQ" wrote in message
...
I wondered about the text but I didn't know how to get it to give me total
hours. I tried yours and My start time is 12/20/2009 9:43 and my end
time is 12/20/2009 10:43. The total hours for the row is showing 0:00:00.
I typed in -c3-b3 then clicked on the format menu and selected custom but
there wasn't a [t]:mm in there so I selected [t]:mm:ss. When I look in
the formula bar, there is no format set so I tried typing it in but I keep
getting a calculation error, I don't exactly know what needs " " or ( ) or
[ ]

Linda




"Per Jessen" wrote in message
...
Hi Linda

You can not sum on 'Text' cells

To calculate hours worked use:

=C3-B3

and format column D as custom [t]:mm

Hopes this helps.
...
Per

"Linda RQ" skrev i meddelelsen
...
Hi Everyone,

Using Excel 2003. I am calculating my hours worked. I enter in the
start date and time in the B column and the end date and time in the C
column. I use the shortcut Ctrl : <space Ctrl+Shft+ : to enter this. I
have = TEXT(C3-B3,"h:mm") This is working fine.

At the bottom of my D column I have this =SUM(D2:D16) but it is
returning 00:00:00.

I tried using the format cell/custom and picked the hours and minutes as
well as other formats but I can't get it to show up.

I need the total hours and minutes in cell D17

Thanks,
Linda








  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 6
Default Total Hours

That worked perfect Sheeloo. Thankyou

Linda

"Sheeloo" wrote in message
...
To see the difference, in hours (in decimal format), in Col D simply enter
this formula in D1 and copy down
=(C1-B1)*24

Then you can add up Col D to get total hours.

"Linda RQ" wrote:

Hi Everyone,

Using Excel 2003. I am calculating my hours worked. I enter in the
start
date and time in the B column and the end date and time in the C column.
I
use the shortcut Ctrl : <space Ctrl+Shft+ : to enter this. I have =
TEXT(C3-B3,"h:mm") This is working fine.

At the bottom of my D column I have this =SUM(D2:D16) but it is returning
00:00:00.

I tried using the format cell/custom and picked the hours and minutes as
well as other formats but I can't get it to show up.

I need the total hours and minutes in cell D17

Thanks,
Linda





.



  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 6
Default Total Hours

This worked as well as the suggestion Sheeloo gave.

Thanks,
Linda

"Bernard Liengme" wrote in message
...
Per is from Denmark where the word for hours must begin with a t
If you are working in English use [h]:mm
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP


"Linda RQ" wrote in message
...
I wondered about the text but I didn't know how to get it to give me
total hours. I tried yours and My start time is 12/20/2009 9:43 and my
end time is 12/20/2009 10:43. The total hours for the row is showing
0:00:00. I typed in -c3-b3 then clicked on the format menu and selected
custom but there wasn't a [t]:mm in there so I selected [t]:mm:ss. When
I look in the formula bar, there is no format set so I tried typing it in
but I keep getting a calculation error, I don't exactly know what needs "
" or ( ) or [ ]

Linda




"Per Jessen" wrote in message
...
Hi Linda

You can not sum on 'Text' cells

To calculate hours worked use:

=C3-B3

and format column D as custom [t]:mm

Hopes this helps.
...
Per

"Linda RQ" skrev i meddelelsen
...
Hi Everyone,

Using Excel 2003. I am calculating my hours worked. I enter in the
start date and time in the B column and the end date and time in the C
column. I use the shortcut Ctrl : <space Ctrl+Shft+ : to enter this.
I have = TEXT(C3-B3,"h:mm") This is working fine.

At the bottom of my D column I have this =SUM(D2:D16) but it is
returning 00:00:00.

I tried using the format cell/custom and picked the hours and minutes
as well as other formats but I can't get it to show up.

I need the total hours and minutes in cell D17

Thanks,
Linda










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
add 10,0015:15 hours to 03:53 Hours to a total time Alan Excel Worksheet Functions 5 September 24th 09 04:25 PM
How do you put Days and Hours into total hours [email protected] Excel Discussion (Misc queries) 11 January 21st 09 06:29 PM
Converting total number of hours (24 hours) into days MV Rao Excel Discussion (Misc queries) 1 January 24th 08 01:50 PM
total hours vs total worked hours tryingtolearn Excel Discussion (Misc queries) 1 November 19th 06 11:24 PM
How to seperate OT and Reg. Hours from total hours cmc1111 Excel Worksheet Functions 6 August 23rd 06 05:18 PM


All times are GMT +1. The time now is 03:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"