I am starting to get an error saying 'The workbook you are pasting to uses a different date system' when using a create PDF/email macro. I have noticed that the date fields on the PDFs created a 4 years and 1 day off from the dates on the spreadsheet. The error gives me the option of Convert or Dont Convert, but either way it changes all the dates to 4 years back. How can I fix this.
Thanks.
Code:
Please Login or Register to view this content.
i would think its because mac uses 1904 date system you probably need to make the excel sheet use the same system in options
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hi,
Because you're using a Mac your default base date system is January 1st 1904. The standard used by Windows is Jan 1st 1900. It's all explained here http://support.microsoft.com/kb/180162
Change your Excel system date as described in the article.
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.
I deselected the Use 1904 Date System, which changed all my dates. I then manually changed them all back to what they should be and it seems to have fixed it, however i have since come across another issue. Since clicking convert on that Date Error message it appears to have messed up my record search. I have attached my spreadsheet. On the Search sheet towards the bottom at the end of the records it has added all these other records which are identical. In one of the columns it has changed all the data to the date 01/00/00 (a column that was never a date to begin with) which is really strange. How can I undo this?
Hi,
I assume you're referring to column E.
The whole of column E is formatted as Dates. Why I've no idea since as you say there will never be dates in here. However it's fact that they are so formatted.
What you're seeing is simply the VLOOKUP returning the values zero since for example in E297 the A290 value inReturnData! does not exist in ReturnData! column B.
Just format column E as General.
I changed the column to general, which changed the dates to 0, however the repeated records still appear. In the current state rows 297 on should be blank (since there are not that many records), but its adding those duplicated records for some reason.
that's because its matching "" with "" in returndata b287
It didnt used to do this. How can I fix it?
well you couldnt have had "" in there because it will match change one of the iferrors to 0 or - or " " instead of ""
I got the formulas from from Matt Paul on youtube: http://www.youtube.com/watch?v=Tr8Rwh8h1xM
It worked flawlessly until the date base issue can up, and all these extra records appeared.
well "" will match see attached
Im not quite sure what I should change?
It seems to be because the cells are returning 0 when no data is found. How can I keep it blank, maybe that will fix it?
you could change return data a7 to
=IFERROR(IF($D7="", " ", $A6+1), "")
Awesome, that fixed it. Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks