Hi,
In Excel 2007, I am trying to make calculation on cells containing time in [h]:mm format. However, it doesn't work when the result is negative, and get only ####### instead! Is there any way to obtain the proper negative result?
You could look at using the 1904 date system which I believe allows negative time values. Take care when you change date system though as it will adjust any existing dates that you have in your workbook.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Actually u can use 1904 date system in order to see negative time result
Люди, питающие благие намерения, как раз и становятся чудовищами.
Regards, «Born in USSR»
Vusal M Dadashev
Baku, Azerbaijan
The underlying "result" is correct - it's simply that on 1900 date system you can not Format the result cells as Time (ie format to General it will show the number).
I would be very (very) wary of switching to 1904 system...
What you should do will depend on key requirement of value - if it is for use in latter calcs then Format cells as General/Number, if the cell format itself is key store the value as Text, eg:
=REPT("-",(timecalc)<0)&TEXT(ABS(timecalc),"[hh]:mm")
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Or u can use formula instead
but if negative your result would be as text=IF(EndTime>StartTime,EndTime-StartTime,"-"&TEXT(ABS(EndTime>StartTime),"[h]:mm"))
Люди, питающие благие намерения, как раз и становятся чудовищами.
Regards, «Born in USSR»
Vusal M Dadashev
Baku, Azerbaijan
Hi,
You could wrap the time calculation in an ABS() function so that it will always return a positive value.
HTH
Richard Buttrey
If this was useful then please rate it appropriately.
Click the small star iconat the bottom left of my post.
Obviously there are implications to using the 1904 system, i.e. changing existing dates and problems linking to workbooks on the 1900 system, but I wouldn't say it's an approach I would avoid.Originally Posted by DonkeyOte
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
99 times out of 100 the switch from 1900 to 1904 is done purely for sake of presentation which IMO is ill advised given the presentational issues can be avoided via some pretty basic workarounds on a 1900 system.
However, as the number of posts on this thread bears testament - this is very much "each to their own" subject matter.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
My particular 2p's worth on this is to avoid switching.
In the past I've encountered real frustrations with people creating files on a Mac which uses the 1904 date system and then having to have code which recognises this, checks what the live application is set to and adjust if necessary.
I've even seen corporate environments where computers are not standardised with some staff using PCs and some Macs. Now that creates a real can of worms when they try to read each others files
Rgds
Richard Buttrey
If this was useful then please rate it appropriately.
Click the small star icon
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks