• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unable to minus the hour - #value

Dear expert,

Is there anyone who can assists on my files that I am trying to minus the hours but the results is #value?

I have tried the rest of the hours deduction but only this questions that bother me with why?

Thanks.
 

Attachments

AliGW

Active Member
The first of the two is text, not a value - change the formatting of the cells to general, and you will see the difference. How is that first value derived in the real workbook?
 
Dear AliGW and vletm,

The 70:91 is derived from accumulating the machine hour, daily. Our manager is asking us to do a weekly report from this week minus the last week.
That means it is not possible to minus which is more than 60 minutes?

If not possible, then I have to do a round up to 71:00 - 59:09 = 11:42.

Many thanks.
 

vletm

Excel Ninja
Falinaicare
How You have gotten time as 70:91?
What kind of clock there are using?
Time can be over 60 minutes - but minutes with hours can be max 59 - after that it will be hour.
Ps. 71:00 - 59:09 isn't it 11:51? ... or how did You get Your 11:42?
 
Falinaicare
How You have gotten time as 70:91?
What kind of clock there are using?
Time can be over 60 minutes - but minutes with hours can be max 59 - after that it will be hour.
Ps. 71:00 - 59:09 isn't it 11:51? ... or how did You get Your 11:42?
Thanks vletm for highlighting. You are correct as it is my typo error. The answer is 11:51.
 

Attachments

pecoflyer

Active Member
FYI avoid applying manual alignment to columns. As text values align left and numbers align right, manual alignment will hide that fact and you will have trouble discovering errors. (if any)
 

vletm

Excel Ninja
Falinaicare
... but still there is something
... because You've asked 'time', which looks 'time' ( 70:91), which has too many minutes.
>> Excel won't understand those times and that's why Excel gives You a hint #value. <<
If it would be more like correct time but text ... then Excel could better use it.
That means, for some reason You've calculated Your times 'Your way' ... other than common way.
 
vletm

Thank you very much for your explanation.

Can I ask is there a conditional formatting to tell to the other staffs that, the time of 70:91 is invalid due to more than 60 minutes?
 
FYI avoid applying manual alignment to columns. As text values align left and numbers align right, manual alignment will hide that fact and you will have trouble discovering errors. (if any)
Do you mean that I have to use the function TEXT? Sorry as I don;t get your meaning.
 

pecoflyer

Active Member
Do you mean that I have to use the function TEXT? Sorry as I don;t get your meaning.
No. XL "naturally" aligns text values left and number values right in cells. Makes them easy to recognize ( unless you manually aligned as in your example sheet)
XL only knows text and numbers, all the rest is how the data is presented to the user, but the underlying value is always text or numbers e.g. XL can show one hour as 01:00 but in the background it is 0,04166667 (1/24th of a daya day being 1 to XL)
 

vletm

Excel Ninja
Falinaicare
You asked about conditional formatting ...
My opinion is that none conditional formatting won't help in that case.
If users can write something as they has done, then that's too late.
Do users someway calculate those values?
> if Yes - then that would be a solution -- to learn how to calculate.
Is there many times - where to calculate those values?
eg (6-5) + (10-8) + (15-12) + (22-18) = 10
>> let Excel calculate those.
Above means, that '70:91' can itself has already many mistakes ... too many!
 

abhi0300

New Member
vletm

Thank you very much for your explanation.

Can I ask is there a conditional formatting to tell to the other staffs that, the time of 70:91 is invalid due to more than 60 minutes?
You can use Data Validation to restrict users to enter anything more than 59 for minutes.

Data > Data Validation > Custom > Formula

=MINUTE(A1)<=59

A1 is the cell where user is entering that time. Copy this cell once you've tested it, select the target range, Paste Special > Validation.

HTH
 
You can use Data Validation to restrict users to enter anything more than 59 for minutes.

Data > Data Validation > Custom > Formula

=MINUTE(A1)<=59

A1 is the cell where user is entering that time. Copy this cell once you've tested it, select the target range, Paste Special > Validation.

HTH
abhi0300
Thank you very much for your proposal. It is working well.
 

vletm

Excel Ninja
Falinaicare
... and the basic challenge will stay -
cause ... consequence
You are trying to minimize the result,
but the reason still remains there ( how those so call minutes has gotten? )
Falinaicare
Above means eg 0:33 + 0:33 + 0.33 = 1:39
 
Last edited:
vletm
Thanks for highlighting.

I am trying to minimize the issue that cause from the user who key in more than 60 minutes. At least if the conditional formatting is not suitable then data validation might be a great solution.
 
Top