Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add 10,0015:15 hours to 03:53 Hours to a total time | Excel Worksheet Functions | |||
How do you put Days and Hours into total hours | Excel Discussion (Misc queries) | |||
Converting total number of hours (24 hours) into days | Excel Discussion (Misc queries) | |||
total hours vs total worked hours | Excel Discussion (Misc queries) | |||
How to seperate OT and Reg. Hours from total hours | Excel Worksheet Functions |