paste negative time with PasteSpecial

Hello,

how can I caste an degative time for excample -5:00 from one excel sheet to a other sheet.

When I paste "-5:00", I'll get "-0,208333333333"


Currently, I use the PateSpecial-Function, xlPasteValues.

What con I do?

Regards,

Sylvian

Your result is 5 hours as a proportion of a day displayed as a number, but I
don't see how you get a negative time in the first place. Negative times are
displayed as ####### in Excel.
--
Ian
--
"Sylvian" <[email protected]> wrote in
message news:[email protected]...
>
> Hello,
>
> how can I caste an degative time for excample -5:00 from one excel
> sheet to a other sheet.
>
> When I paste "-5:00", I'll get "-0,208333333333"
>
>
> Currently, I use the PateSpecial-Function, xlPasteValues.
>
> What con I do?
>
> Regards,
>
> Sylvian
>
>
> --
> Sylvian
> ------------------------------------------------------------------------
> Sylvian's Profile:
> http://www.excelforum.com/member.php...o&userid=26730
> View this thread: http://www.excelforum.com/showthread...hreadid=399832
>

XL has no problem displaying negative times using the 1904 date system
(MacXL default) - choose Tools/Custom/Calculation, check the 1904 Date
system checkbox.

In article <[email protected]>, "Ian" <[email protected]>
wrote:

> Your result is 5 hours as a proportion of a day displayed as a number, but I
> don't see how you get a negative time in the first place. Negative times are
> displayed as ####### in Excel.

I didn't realise that. Thanks for the pointer. However, I still can't get
negative times to display as negative numbers. -05:00:00 is shown in the
formula bar, but 05:00:00 appears in the cell (Windows XL 2002).

--
Ian
--
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> XL has no problem displaying negative times using the 1904 date system
> (MacXL default) - choose Tools/Custom/Calculation, check the 1904 Date
> system checkbox.
>
> In article <[email protected]>, "Ian" <[email protected]>
> wrote:
>
>> Your result is 5 hours as a proportion of a day displayed as a number,
>> but I
>> don't see how you get a negative time in the first place. Negative times
>> are
>> displayed as ####### in Excel.

I used a custom format of:
hh:mm:ss

and the negative time showed up nicely.

What's the numberformat for your cell?

Ian wrote:
>
> I didn't realise that. Thanks for the pointer. However, I still can't get
> negative times to display as negative numbers. -05:00:00 is shown in the
> formula bar, but 05:00:00 appears in the cell (Windows XL 2002).
>
> --
> Ian
> --
> "JE McGimpsey" <[email protected]> wrote in message
> news:[email protected]...
> > XL has no problem displaying negative times using the 1904 date system
> > (MacXL default) - choose Tools/Custom/Calculation, check the 1904 Date
> > system checkbox.
> >
> > In article <[email protected]>, "Ian" <[email protected]>
> > wrote:
> >
> >> Your result is 5 hours as a proportion of a day displayed as a number,
> >> but I
> >> don't see how you get a negative time in the first place. Negative times
> >> are
> >> displayed as ####### in Excel.

--

Dave Peterson

Ah, that was it. I was using the default time format, which appears to be
the only one with this problem. However I can't input a -ve time eg -5:00. I
can only get the negative time by entering a -ve number then changing the
format to time.

I'm not too worried about this as I've never needed a -ve time. I'm just
curious s to why there are these quirks.
--
Ian
--
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>I used a custom format of:
> hh:mm:ss
>
> and the negative time showed up nicely.
>
> What's the numberformat for your cell?
>
> Ian wrote:
>>
>> I didn't realise that. Thanks for the pointer. However, I still can't get
>> negative times to display as negative numbers. -05:00:00 is shown in the
>> formula bar, but 05:00:00 appears in the cell (Windows XL 2002).
>>
>> --
>> Ian
>> --
>> "JE McGimpsey" <[email protected]> wrote in message
>> news:[email protected]...
>> > XL has no problem displaying negative times using the 1904 date system
>> > (MacXL default) - choose Tools/Custom/Calculation, check the 1904 Date
>> > system checkbox.
>> >
>> > In article <[email protected]>, "Ian" <[email protected]>
>> > wrote:
>> >
>> >> Your result is 5 hours as a proportion of a day displayed as a number,
>> >> but I
>> >> don't see how you get a negative time in the first place. Negative
>> >> times
>> >> are
>> >> displayed as ####### in Excel.
>
> --
>
> Dave Peterson

Sylvian,

Presumably, you're using PasteSpecial - Values because it's a formula you've
copied, and you want the result, not the formula. If so, it's not pasting
the time formatting. So you're getting the time value, unformatted. Add
PasteSpecial xlPasteFormats after your PasteSpecial xlPasteValues.
--
Earl Kiosterud
www.smokeylake.com

"Sylvian" <[email protected]> wrote in
message news:[email protected]...
>
> Hello,
>
> how can I caste an degative time for excample -5:00 from one excel
> sheet to a other sheet.
>
> When I paste "-5:00", I'll get "-0,208333333333"
>
>
> Currently, I use the PateSpecial-Function, xlPasteValues.
>
> What con I do?
>
> Regards,
>
> Sylvian
>
>
> --
> Sylvian
> ------------------------------------------------------------------------
> Sylvian's Profile:
> http://www.excelforum.com/member.php...o&userid=26730
> View this thread: http://www.excelforum.com/showthread...hreadid=399832
>

Well the question I have is how do you program it not to be a negative number?

If I have a column with -3,-67,-80 etc. What is the VBA code to put them all to positives? ie 3,67,80 etc?

Thanks

You could loop through the cells.

Or (manually) put -1 in an empty cell
edit|copy (that cell)
select your range of negative numbers
edit|pastespecial|check multiply
clear the contents of that helper cell.

(record a macro when you do it once)

Or to loop:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

Set myRng = Selection

For Each myCell In myRng.Cells
If IsEmpty(myCell) Then
'do nothing
Else
If IsNumeric(myCell.Value) Then
If myCell.Value < 0 Then
myCell.Value = -1 * myCell.Value
End If
End If
End If
Next myCell

End Sub

Select your range and run the macro.


Fastbike wrote:
>
> Well the question I have is how do you program it not to be a negative
> number?
>
> If I have a column with -3,-67,-80 etc. What is the VBA code to put
> them all to positives? ie 3,67,80 etc?
>
> Thanks
>
> --
> Fastbike
> ------------------------------------------------------------------------
> Fastbike's Profile: http://www.excelforum.com/member.php...o&userid=26701
> View this thread: http://www.excelforum.com/showthread...hreadid=399832

--

Dave Peterson

That is all very logiacl and makes sense.

Thanks for the advice it is much appreciated.

Fastbike

The sheets are in the same workbook or different workbooks!

First: I copy the data

Second: I open a new workbook

Third: Paste the data

Pasting positive times is working perfect, but the negative ones...

Best Regards,

Sylvian

For the guy who wanted to know how to change negative to posative numbers...

You can always use the ABS() function to give you the absolute value of any number... i.e. ABS(-234) returns 234 and ABS(234) returns 234 as well.

Just another path to the same goal

Make sure both workbooks use the same base date (1904).

Tools|options|calculation tab

maybe...I'm not sure what the problem is.

Sylvian wrote:
>
> The sheets are in the same workbook or different workbooks!
>
> First: I copy the data
>
> Second: I open a new workbook
>
> Third: Paste the data
>
> Pasting positive times is working perfect, but the negative ones...
>
> Best Regards,
>
> Sylvian
>
> --
> Sylvian
> ------------------------------------------------------------------------
> Sylvian's Profile: http://www.excelforum.com/member.php...o&userid=26730
> View this thread: http://www.excelforum.com/showthread...hreadid=399832

--

Dave Peterson

Hello Dave,

I don’ want to convert the negative time into a positive one!

Sylvian

Worksheet1.Range("a7:q39").Copy

Dim sName As String
sName = "d:\…\" & Worksheet1.tbName.Value & ".xls"
Workbooks.Open sName

ActiveWorkbook.ActiveSheet.Name =Worksheet1.tbName.Value

If Worksheet1.Range("o5").Value = "01.05.2005" Then
Worksheet1.paste Destination:=ActiveWorkbook.ActiveSheet.Range("a160")
With ActiveWorkbook.ActiveSheet
.Range("a160").PasteSpecial paste:=xlPasteValues
.Range("a160").PasteSpecial paste:=xlPasteFormats
End With