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


--
Sylvian
------------------------------------------------------------------------
Sylvian's Profile: http://www.excelforum.com/member.php...o&userid=26730
View this thread: http://www.excelforum.com/showthread...hreadid=399832

paste negative time with PasteSpecial
 

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" wrote in
message ...

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

paste negative time with PasteSpecial
 

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 , "Ian"
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.

paste negative time with PasteSpecial
 

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" wrote in message
...
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 , "Ian"
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.

paste negative time with PasteSpecial
 

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" wrote in
message ...

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

paste negative time with PasteSpecial
 

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" wrote in message
...
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 , "Ian"
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

paste negative time with PasteSpecial
 

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" wrote in message
...
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" wrote in message
...
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 , "Ian"
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

paste negative time with PasteSpecial
 

you could use:

=-time(5,0,0)





Ian wrote:

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" wrote in message
...
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" wrote in message
...
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 , "Ian"
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

--

Dave Peterson

paste negative time with PasteSpecial
 

Thanks. That works a treat.

--
Ian
--
"Dave Peterson" wrote in message
...
you could use:

=-time(5,0,0)





Ian wrote:

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" wrote in message
...
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" wrote in message
...
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 , "Ian"

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

--

Dave Peterson

paste negative time with PasteSpecial
 

Hello,

how can I paste an negative time for excample -5:00 from one exce
sheet to a other sheet.

When I paste "-5:00", I'll get “#############” which is equal t
"-0,208333333333"


Currently, I use the PateSpecial-Function, xlPasteValues.

What can I do? Where is my mistake?

XL has no problem displaying negative times using the 1904 date system
(MacXL default) - choose Tools/Custom/Calculation, check the 1904 Date
I don’t find this!

When I use PasteSpecial xlPasteFormats after PasteSpecia
xlPasteValues. It is not working.

When I use a custom format of:
hh:mm:ss, It is also not working out.

Regards,

Sylvia

--
Sylvia
-----------------------------------------------------------------------
Sylvian's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=39983

paste negative time with PasteSpecial
 

Are the sheets in the same workbook or different workbooks?

If they're in different workbooks, then remember you'll have to toggle that
setting under:

Tools|options|calculation tab|check "1904 date system"

before you can format that negative time to look nice.

Sylvian wrote:

Hello,

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

When I paste "-5:00", I'll get “#############” which is equal to
"-0,208333333333"

Currently, I use the PateSpecial-Function, xlPasteValues.

What can I do? Where is my mistake?

XL has no problem displaying negative times using the 1904 date system
(MacXL default) - choose Tools/Custom/Calculation, check the 1904 Date…
I don’t find this!

When I use PasteSpecial xlPasteFormats after PasteSpecial
xlPasteValues. It is not working.

When I use a custom format of:
hh:mm:ss, It is also not working out.

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

paste negative time with PasteSpecial
 

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

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

Thank

--
Fastbik
-----------------------------------------------------------------------
Fastbike's Profile: http://www.excelforum.com/member.php...fo&userid=2670
View this thread: http://www.excelforum.com/showthread.php?threadid=39983

paste negative time with PasteSpecial
 

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

paste negative time with PasteSpecial
 

That is all very logiacl and makes sense.

Thanks for the advice it is much appreciated.

Fastbik

--
Fastbik
-----------------------------------------------------------------------
Fastbike's Profile: http://www.excelforum.com/member.php...fo&userid=2670
View this thread: http://www.excelforum.com/showthread.php?threadid=39983

paste negative time with PasteSpecial
 

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,

Sylvia

--
Sylvia
-----------------------------------------------------------------------
Sylvian's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=39983

paste negative time with PasteSpecial
 

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

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

Just another path to the same goa

--
kralj
-----------------------------------------------------------------------
kraljb's Profile: http://www.excelforum.com/member.php...nfo&userid=995
View this thread: http://www.excelforum.com/showthread.php?threadid=39983

paste negative time with PasteSpecial
 

Hello Dave,

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

Sylvian


--
Sylvian
------------------------------------------------------------------------
Sylvian's Profile: http://www.excelforum.com/member.php...o&userid=26730
View this thread: http://www.excelforum.com/showthread...hreadid=399832

paste negative time with PasteSpecial
 

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.past
Destination:=ActiveWorkbook.ActiveSheet.Range("a16 0")
With ActiveWorkbook.ActiveSheet
.Range("a160").PasteSpecial paste:=xlPasteValues
.Range("a160").PasteSpecial paste:=xlPasteFormats
End Wit

--
Sylvia
-----------------------------------------------------------------------
Sylvian's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=39983

paste negative time with PasteSpecial
 

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

paste negative time with PasteSpecial
 

Thank's to all it is working :)

But, How can I program a macro which opens a workbook and inserts the
1904-DateFormat automatically?

Sylvian


--
Sylvian
------------------------------------------------------------------------
Sylvian's Profile: http://www.excelforum.com/member.php...o&userid=26730
View this thread: http://www.excelforum.com/showthread...hreadid=399832

paste negative time with PasteSpecial
 

Before you do this, realize that any dates in your that workbook will be off by
4 years and one day. And copying a date between workbooks with different base
dates will screw up that date.

If you're using a PC, most people use the 1900 base date. If you're using a
Mac, I think most use the 1904 base date.

If most people you share work with use the 1900 base date, then I would use this
very sparingly.

I recorded a macro and got this salient line:

ActiveWorkbook.Date1904 = True

If you want to open a workbook and use this line, you could use a macro like:

Option Explicit
Sub testme()
Dim wkbk As Workbook
Set wkbk = Workbooks.Open(Filename:="C:\my documents\excel\book1.xls")
wkbk.Date1904 = True
End Sub

(Change the name of the workbook to what you need.)

But once you toggle this setting, it should stay put until someone changes it.
(I'm not sure I'd use a macro for this.)

Sylvian wrote:

Thank's to all it is working :)

But, How can I program a macro which opens a workbook and inserts the
1904-DateFormat automatically?

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