How can I caste an negative time for example -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.
How can I caste an negative time for example -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.
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.
XL has no problem displaying negative times using the 1904 date system (MacXL default) - choose Tools/Custom/Calculation, check the 1904 Date system checkbox. I didn't realize 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).
Presumably, you're using PasteSpecial - Values because it's a formula you have 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.
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 am not too worried about this as I've never needed a -ve time. I am just curious s to why there are these quirks.
Bookmarks