[Resolved]Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Hi,

I am extracting values in the format of hh:mm:ss from one spreadsheet and storing it into a string
i then add the value stored in that string to another value stored in another string(hh:mm:ss)

when i then paste the new value into another workbook, the format becomes
dd/mm/yy hh:mm:ss
and in the example below the format of the destination cell becomes "scientific", although i had already set the format to hh:mm:ss

do you know how i can sort this, is it because i am adding variables using cells?

here is an example of part of the code, as this is kinda hard to explain
the variables that end in "t" are my temp variables

VB Code:

'location of unavailable
    ranPers.Cells(201).Select
    strUnavailt = ActiveCell.Value
    Range("K1").Value = strUnavailt
    Range("K2").Value = strUnavail
    Range("K3").Formula = "=sum(K1:K2)"
    strUnavail = Range("K3").Value
    Range("K1:K3").Clear

does this help?

VB Code:

Private Sub CommandButton1_Click()
 
'Extracting value from a cell
str1 = Sheets("Sheet1").Range("A1").Value
 
'Say a string which has a value in "hh:mm:ss" format
str2 = "08:00:00"
 
'Adding them
Sum = Val(Format(str1, "0.00")) + Val(Format(str2, "0.00"))
 
'storing the sum in another cell in another sheet
Sheets("Sheet2").Range("A10").Value = Sum
'changing the format of this cell to "hh:mm:ss" format
Sheets("Sheet2").Range("A10").NumberFormat = "hh:mm:ss"
 
 
End Sub

cheers mate

when i change the format to hh:mm:ss it remains the other way

i right click and edit the cells properties

what my sub does is go through the worksheet and adds up certain cells depending on whether the title of the range meets the criteria
as its loopin its doin the above code

is it the way vba is storin the code?

when the macro has run

if i format the cell to be hh:mm:ss
it remains in the format of dd/mm/yy hh:mm:ss

is this because it may have exceded 24 hours?
which it more than likely has....

it's in work, i will go in tomorrow and email it to myself
then i will up it

but it basically loops and counts up all the unavailable time for each individual on each time
ie team 1
it will sum all the unavailable time for team one, and will store it in strUnavail

Oh... Ok

try this

VB Code:

Private Sub CommandButton1_Click()
 
'Extracting value from a cell
str1 = Sheets("Sheet1").Range("A1").Value
 
'Say a string which has a value in "hh:mm:ss" format
str2 = "08:00:00"
 
'Adding them
Sum = Val(Format(str1, "0.00")) + Val(Format(str2, "0.00"))
 
'storing the sum in another cell in another sheet
Sheets("Sheet2").Range("A10").Value = Sum
'changing the format of this cell to "[h]:mm:ss" format
'so it shows the sum....
Sheets("Sheet2").Range("A10").NumberFormat = "[h]:mm:ss"
 
End Sub

Does this help?

ps: Are you working in a contact centre?

yeah
we use seagate enterprise reports
but they wont change them as we are getting rid of rockwell at the end of the year

Quote:

---

yeah
we use seagate enterprise reports
but they wont change them as we are getting rid of rockwell at the end of the year

---

Me too :D
rockwell Issss... pathetic...
I think now your problem will be solved... check it out...
Have changed the format to "[h]:mm:ss"

what do the [ ] 's do to the formatting?

we're going to cisco(sP) the back end of 2007
so no development will be happenin with any rockwell reports

its better in the long run as we will be getting business objects, i will test this out when i'm next in work
not gonna go in tomorrow, cant be bothered lol

If you are working with times and you want Excel to display hours greater than 24, use the custom format [h]:mm:ss

sound

that will do me, tar for that mate
do i just rate one post or all?

i'll check in work, when i'm next in then i will update

cheers

heres attached my files

cant suss it out

doesnt appear to be calculating AHT and total handled time correctly...

oops!
RTM

^pass