• 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.

Date format dd/mm/yyyy won't work with some dates

Rodrigues

Member
All
Firstly credit to Belleke, for the help in solving this problem, however haven't tested until now date with leading zeros.
The problem I have is that, when I use 01/02/18 (1st of Feb) on Reg11 (Date Completed), on Reg13 (Due In) the calculation (reg11+Reg12 Frequency ) is not correct.
Seems that, dates from:
Days: 01/03/04/05/06/07/08/09/10/11/12 of Feb fails;
Days: 02/13/14/15/16/17/18/19/20/21/22/23/24/25/26/27/28 of Feb- OK
Tested with Marsh and it's the same.
Could anyone help me please?
Thanks in advance.
Regards
R
 

Attachments

  • Book.xlsm
    82.2 KB · Views: 3
Rodrigues
If You would like to use 'Dates' then format cells as 'Date' and
after that take care that You won't change format to other ... ex text.
Below - only those two dates are dates other are not!
Screen Shot 2018-02-19 at 19.23.16.png
Many times Excel can try to figure users marks correct ... sometimes not!
 
Hi Vletm
Sorry to disturb you, I think I have format the cells correctly as per yours instructions, however still the same issue.
Sheet2 columns A & B are formatted to Long date just for test.
Could you please have a look and advise?
Thank you so much
Regards
R
 

Attachments

  • Book - Copy.xlsm
    129.9 KB · Views: 3
Rodigues

In Data!AO12 type =ISNUMBER(AO7)
copy that down

You will now see that AO7 is not a date it is text
AO8:AO10 are dates
same in column AQ

upload_2018-2-20_9-8-47.png

To fix simply select AO7
Press F2
Then press Enter

repeat for AQ7

Same issue for M7 and O7

Dates are stored as integer numbers in Excel
1 = 1 Jan 1900
43,151 = 20 Feb 2018

The custom number formats set how the date is displayed in Excel
eg:
d mm yyyy = 20 02 2018
d mmm yy = 20 Feb 18
etc

Excel is smart enough to understand that if you type in a date as
20/2/18 it is Feb 20, 2018 and stores it internally as 43151, but displays it according to the format of the cell
 
Last edited:
Rodrigues
Hui wrote already many points ...
Your newest file has few more 'interesting points'.
Sorry to write that ... Excel do not matter of thinking.

Cells M7 & O7 had case ... I would name 'locked'.
If someone has done mistake.. mistake.. mistake ..
then even Excel cannot figure users needs.
Those 'locks' could open only by DELETE those values
(remember to press <ENTER> too)
and REWRITE values correct!

... what was Your 'the same issue'?

1) if You would like to use 'date' then write as 'date'
2) if You have challenges then - delete > enter > rewrite
3) if You use dates then use those as dates (not as text)
in formulas and especially with VBA.
(same ideas works with times)
 

Hi !

A tip : hit Ctrl 3 (formula view toggle) so true dates become numbers
and false dates stay as text …
 
Hi all
I have been working on this and trying to fully understand and resolve without success, I'm sorry about my little knowledge on this, so here he goes a few more questions if you guys don't mind.
I understand now, in some cells the date are not format as a date even thought the cells showing as dd/mm/yyy (if I understood correctly, this is only to display the date as... and not to format).
- To key in and record the date as dd/mm/yyy on the userform what do I need to do? At the moment I'm doing as: 01/02/18 (01Feb18) or 20/02/18 and (20Feb18) and hit ENTER;
While adding the date on Reg11 (userform) shows fine, however on Reg13 is not;
On the list box some shows correct some don't, however when double click on list box Reg11 shows correct and on Reg13 not.

- Have added a piece of code called (sub date format) to format M ; O ; AO ; AQ, but clearly is not working, after saving the code and add data onto the userform columns A & B ; AT & AU shows False/True.

I would like to be able to do that, on Reg11 key in a date I.e.: 01/02/18 (1stFeb18) then on Reg12 select the frequency and on Reg13 have the calculation.
Example 01/02/2018 + 30 = 03/03/2018 and so on.
Again I do apologies to bother you all with this but I can't figured out how to accomplish this.
Please help.
Thanks again.
Regards
R
 

Attachments

  • Book - Copy.xlsm
    140.6 KB · Views: 3
Rodrigues
I can try to give one more hint:
Run Your challenge in small steps ...
follow Your 'dates' in every steps and
try to figure when something seems to change to other 'format' than dates.
If it changes ... do it change back to date (for sheet).
Every step means ... every steps!
For me, Your form didn't do nothing! ... but that's normal!
Good Luck!
 
All
Thank you so much.
vletm, when you say the form didn't do nothing, do you mean the dates or the form itself?

Thanks again to All, very much appreciated.
Regards
R
 
Rodrigues
I could open Your form,
after that, I only tried to check/test Your 'Reg11..13' --- empty.
If it works with You ... okay, for You ...
but I would do that kind of form ... step-by-step.
 
Back
Top