Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time over 24 hours
I'm creating a custom timecard to compute time spent by a given employee on
various projects. When I try to add daily totals together to get weekly totals and that total exceeds 23:59, I can't find a format that will display it as such -- it always reduces it to a time under 23:59. (Example: 14:30 + 12:18 = 26:48, but it displays as 2:48) How can I fix this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time over 24 hours
Format Custom as [h]:mm
-- Kind regards, Niek Otten "CraigR53" wrote in message ... I'm creating a custom timecard to compute time spent by a given employee on various projects. When I try to add daily totals together to get weekly totals and that total exceeds 23:59, I can't find a format that will display it as such -- it always reduces it to a time under 23:59. (Example: 14:30 + 12:18 = 26:48, but it displays as 2:48) How can I fix this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time over 24 hours
Hi,
I tryed this and it worked, hope it works for you to 14:30 (Cell A1) 12:18(Cell B1) A1*24 (Cell A2) B1*24(Cell B2) TRUNC(A2) (Cell A3) TRUNC(B2) (Cell B3) A2-A3 (Cell A4) B2-B3 (Cell B4) (A4*60)/100 (Cell A5) (B4*60)/100 (Cell B5) A3+A5 (Cell A6) B3+B5 (Cell B6) TOTAL A6+B6 The cells working the formulas can be hidden by marking the row height to 0 Hope I was helpfull "CraigR53" wrote: I'm creating a custom timecard to compute time spent by a given employee on various projects. When I try to add daily totals together to get weekly totals and that total exceeds 23:59, I can't find a format that will display it as such -- it always reduces it to a time under 23:59. (Example: 14:30 + 12:18 = 26:48, but it displays as 2:48) How can I fix this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time over 24 hours
Much thanks to you both. Niek's answer was what I needed to fix my problem and finish deveoping the timecard. I now have a daily timecard (which, when printed, can fit on one page) on which the user enters "in" time (CTRL+SHIFT+;), task description, and department code. When the task is completed, the user enters "out" time, which then becomes the "In" time for the next task. The spreadsheet imediately updates total daily time for each department as well as total for the day, and updates a weekly summary that also reflects total by department and week, and compares those totals to budgeted times. All I needed was Niek's formula, and about 4 hours. I started using the timecard today, and it seems to work fine. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time and Business Hours | Excel Worksheet Functions | |||
time | Excel Discussion (Misc queries) | |||
calculate difference in time to hours | Excel Worksheet Functions | |||
in excel totaling weekly hours military time | Excel Worksheet Functions | |||
time format multiplied by hours worked ? | Excel Worksheet Functions |