paste negative time with PasteSpecial

[Sylvian]

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

[Ian]

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
>

[JE McGimpsey]

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.

[Ian]

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.

[Earl Kiosterud]

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
>

[Dave Peterson]

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

[Ian]

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

[Dave Peterson]

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" <[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

--

Dave Peterson

[Ian]

Thanks. That works a treat.

--
Ian
--
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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" <[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
>
> --
>
> Dave Peterson

[Sylvian]

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

[Dave Peterson]

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

[Fastbike]

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

[Dave Peterson]

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

[Fastbike]

That is all very logiacl and makes sense.

Thanks for the advice it is much appreciated.

Fastbike

[Sylvian]

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

[kraljb]

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

[Sylvian]

Hello Dave,

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

Sylvian

[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

[Dave Peterson]

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

[Sylvian]

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

[Dave Peterson]

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