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

**Mitch_s_s** · Feb 3rd, 2007, 07:55 AM

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

**Siddharth Rout** · Feb 3rd, 2007, 09:12 AM

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

**Mitch_s_s** · Feb 3rd, 2007, 12:57 PM

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?

**Mitch_s_s** · Feb 3rd, 2007, 01:38 PM

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....

**Mitch_s_s** · Feb 3rd, 2007, 01:53 PM

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

**Siddharth Rout** · Feb 3rd, 2007, 02:04 PM

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?

**Mitch_s_s** · Feb 3rd, 2007, 02:05 PM

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

**Siddharth Rout** · Feb 3rd, 2007, 02:08 PM

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

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

**Mitch_s_s** · Feb 3rd, 2007, 02:25 PM

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

**Siddharth Rout** · Feb 3rd, 2007, 02:35 PM

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

**Mitch_s_s** · Feb 3rd, 2007, 02:36 PM

sound

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

**Mitch_s_s** · Feb 3rd, 2007, 04:21 PM

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

cheers

**Mitch_s_s** · Feb 8th, 2007, 06:22 PM

heres attached my files

cant suss it out

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

**Mitch_s_s** · Feb 9th, 2007, 03:05 AM

oops!
RTM

**Mitch_s_s** · Feb 9th, 2007, 03:05 AM

^pass

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

Thread Tools

Display

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

Re: Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Re: Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Re: Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Re: Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Re: Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Re: Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Re: Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Re: Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Re: Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Re: Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Re: Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Re: Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Re: Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Re: Cell Formats - hh:mm:ss converts to dd/mm/yy hh:mm:ss

Posting Permissions