I'm trying to find the difference in two different times (generally going to be less than a minute apart) and it works fine from noon to 12:59:59 and then it gives me a #VALUE! error. Before 1pm, the time is shown as mm:ss.0 but then after 1pm, it shows up as hh:mm:ss.000 PM and I think that's what the error is. I've been reformatting these cells trying to get them to be the same but nothing is working.
Can anyone help me out?
Hi,
Any chance you could upload the workbook so that we may see in context
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Correct times.jpg Incorrect Times.jpg
The one on the left is correct and the one on the right is where I'm having issues. I don't know how to upload the whole workbook or even if I should be (this is for my work so privacy and all that) so hopefully this is good. The times for the correct one go from 7am to 9am and that's working fine. The incorrect one is from 11am to 1pm and I have another with the same issue that's 4pm to 6pm... I thought the last one would work ok since the times appeared to show up the same but I'm still having issues.
It's a little hard to guess when we can't see the subtrahends.
Entia non sunt multiplicanda sine necessitate
(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)
- Click Advanced (next to quick post),
- Scroll down until you see "Manage Attachments",
- Click that then select "add files" (top right corner).
- Click "Select Files" find your file, click "open" click "upload"
- Once the upload is completed the file name will appear below the input boxes in this window.
- Click "Done" at bottom right to close the Attachment Manager.
- Click "Submit Reply"
Ben Van Johnson
So there's the workbook I'm using. It seems to me like I need the times after 1pm converted to hh:mm:ss to get this to work but changing the formatting is doing absolutely nothing to get rid of the decimal and everything after that.
Last edited by Scootman1911; 10-12-2015 at 05:26 PM.
Hi,
Formatting has nothing to do with this, formatting rarely does since formatting can never change the underlying number.
Your problem is due to the fact that times after 13:00 (Row A1110 and onwards) are text rather than being numbers. i.e. your data is not consistent and needs correcting. the following formula in D6 copied down is one way
Formula:
Please Login or Register to view this content.
but you really would be advised to make sure your underlying data is consistent.
You could fix the input using find & replace.
Select col A, find " *" (sans quotes), leave the replace box empty, press replace all.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks