Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cut, Copy, Paste, PasteSpecial grayed out. | Excel Discussion (Misc queries) | |||
Negative time should be allowed in Excel, eg time difference | Excel Discussion (Misc queries) | |||
PasteSpecial Paste:=ColumnWidths | Excel Programming | |||
Macro to Paste to specific line, and continue to Paste each time on next row not over | Excel Programming | |||
Run-time error '1004' PasteSpecial Method of Range Class Failed | Excel Programming |