I have a cell which calculates the total time worked and it is formatted as hh:mm. When I try and multiply this cell by the number of people who worked I get strange answers even though I have formatted the total field in the same way. Any ideas what I am doing wrong?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
excel multiplying time problem
- Thread starter tizwaz
- Start date
Can you give us some examples? Are any of your total times displaying greater than 24 hours?
Duane
Hook'D on Access
MS Access MVP
Duane
Hook'D on Access
MS Access MVP
SkipVought
Programmer
Hi,
You might find this enlightening.
Date and Time, as you know, having read the above FAQ, are just a number, but in addition to that, they are POINTS in time.
Jan 20 2009 7:45 AM is just that; a point in time.
But when you take the difference between two Date/Time points, you get a DURATION. If your DURATION DateSerial value is 1.5 (a day and a half) Excel displays that as 1/1/1900 12:00:00 PM. Excel has no way of knowing that your Date/Time value is a DURATION, unless you FORMAT as [hh]:mm, in which case the above example would be displayed as 36:00 This takes the integer part of the difference and converts it from days to hours in the display. This is why Duane asked, "Are any of your total times displaying greater than 24 hours?"
Skip,
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Don't let the Diatribe...
talk you to death!![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
Just traded in my old subtlety...
for a NUANCE!
You might find this enlightening.
Date and Time, as you know, having read the above FAQ, are just a number, but in addition to that, they are POINTS in time.
Jan 20 2009 7:45 AM is just that; a point in time.
But when you take the difference between two Date/Time points, you get a DURATION. If your DURATION DateSerial value is 1.5 (a day and a half) Excel displays that as 1/1/1900 12:00:00 PM. Excel has no way of knowing that your Date/Time value is a DURATION, unless you FORMAT as [hh]:mm, in which case the above example would be displayed as 36:00 This takes the integer part of the difference and converts it from days to hours in the display. This is why Duane asked, "Are any of your total times displaying greater than 24 hours?"
Skip,
Don't let the Diatribe...talk you to death!
Just traded in my old subtlety...for a NUANCE!
- Thread starter
- #5
yes my total times would exceed 24 hours. Below is an extract. 1st 2 columns are the times worked. Third is the duration worked. 4th are the no of people working this shift and 5th is total time worked per shift. As you can see it copes with 1 person but for eg 10 x 2 hours 30 mins is shown as 01:00
13:00 16:00 03:00 1 03:00
16:45 19:45 03:00 1 03:00
17:15 19:45 02:30 10 01:00
11:00 13:30 02:30 1 02:30
13:00 16:00 03:00 1 03:00
16:45 19:45 03:00 1 03:00
17:15 19:45 02:30 11 03:30
13:00 16:00 03:00 1 03:00
16:45 19:45 03:00 1 03:00
17:15 19:45 02:30 10 01:00
11:00 13:30 02:30 1 02:30
13:00 16:00 03:00 1 03:00
16:45 19:45 03:00 1 03:00
17:15 19:45 02:30 11 03:30
SkipVought
Programmer
Y
ou have already received your answer.
Skip,
Don't let the Diatribe...talk you to death!
Just traded in my old subtlety...for a NUANCE!
AnotherHiggins
Technical User
(twice)
[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
- Status