+ Reply to Thread
Results 1 to 14 of 14

Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th

  1. #1
    Registered User
    Join Date
    05-30-2015
    Location
    Beijing, China
    MS-Off Ver
    2007
    Posts
    9

    Smile Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th

    Hi

    We use the following date format on our school timetable

    6月15日 星期一 Mon Jun 15th

    It has to be done manually every month which is a bit time consuming and can also lend itself to errors being made.

    We are trying to find a way to get the dates to be automatically input into the table somehow.

    I have found the "format cells/custom" but I am unsure how to use it. I have searched around on the internet for ages but have hit a brick wall trying to find any advice that includes Chinese dates.

    The date we use is 6月15日 星期一 Mon Jun 15th. Which is when translated 6月 (June) 15日 (15th) 星期一(Monday) and then the English - Mon Jun 15th

    Excel does have the option for Chinese style dates, but I don't understand how to make it use them in a way that we want.

    We want the dates from Monday to Friday, no weekends as shown in the attached spreadsheet.

    Blank.xls

    Thank you for any help!

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th

    How about using a VLookup table to find the englisch number (e.g. 6) and "translate" that to the chinese version?
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 365
    Posts
    2,406

    Re: Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th

    Perhaps something like this? pls see the file attach............

    Im adding helper columns (on Sheet1) for the date, so you can change it later if you want.....

    hope this works......
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-30-2015
    Location
    Beijing, China
    MS-Off Ver
    2007
    Posts
    9

    Thumbs up Re: Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th

    Hi azumi

    That is amazing!

    I can't even begin to thank you enough. But I will try, thank you!

    I have no idea how you have done that, the formula crosses two monitors and is still going! Incredible work

    How can I add in another week? What is the information in the formula that needs to be changed?

    We sometimes have months that last 5 weeks - what needs to be changed please?

    I have tried playing around with the formula but I just get errors.

    Regards

  5. #5
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 365
    Posts
    2,406

    Re: Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th

    Hi sorry for late reply

    Im modified formula to include all 5 weeks, just check and more info in it.........


    cheers
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-30-2015
    Location
    Beijing, China
    MS-Off Ver
    2007
    Posts
    9

    Re: Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th

    Hi

    Thank you again. Sorry I have been slow to reply. We have been really busy with our Children's Day performances and I have had very little spare time.

    The sheet is almost perfect

    I am having trouble with the first two weeks, they both come up with the same dates in them.

    In the "help sheet" it seems that there is no week one - it starts at week 2, how can I change this?

    I have attached the version that has been edited a little bit so it is exactly as we well use it.

    Thank you again for you help

    Blank (1).xlsx

  7. #7
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 365
    Posts
    2,406

    Re: Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th

    Just copied (CTRL-C) first date row 3 (start B2 and end on F3), when you done just paste (CTRL-V) on row date below (row 9, 15, 21 and 27, just practice it
    Last edited by azumi; 06-03-2015 at 11:03 AM.

  8. #8
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 365
    Posts
    2,406

    Re: Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th

    If you having difficulties check this out......
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-30-2015
    Location
    Beijing, China
    MS-Off Ver
    2007
    Posts
    9

    Re: Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th



    Thanks again!

    I am about to go to school now - I will report back later

  10. #10
    Registered User
    Join Date
    05-30-2015
    Location
    Beijing, China
    MS-Off Ver
    2007
    Posts
    9

    Re: Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th

    I have given the lesson plan out to some of the teachers, fingers crossed they have no problems with it.

    Thank you for all of your help

  11. #11
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 365
    Posts
    2,406

    Re: Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th

    You're welcome, and thanks for the feedback........

    regards

  12. #12
    Registered User
    Join Date
    05-30-2015
    Location
    Beijing, China
    MS-Off Ver
    2007
    Posts
    9

    Re: Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th

    Hello again

    Table is working well, thank you

    Is it possible to make the dates superscript? We have tried using auto correct and find and replace but can't find a way to make it work.

    Thank you again

  13. #13
    Registered User
    Join Date
    11-15-2019
    Location
    Malaysia
    MS-Off Ver
    2019
    Posts
    1

    Re: Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th

    May I have a copy of the working worksheet? I am very interested to learn this technique. Many thanks

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,047

    Re: Auto fill dates with both Chinese format and English format ie 6月15日 星期一 Mon Jun 15th

    Quote Originally Posted by wllmngty View Post
    May I have a copy of the working worksheet? I am very interested to learn this technique. Many thanks
    The file is available in post #8
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Format a cell to auto fill by clicking
    By dan_fash in forum Excel General
    Replies: 4
    Last Post: 01-22-2015, 02:41 PM
  2. English to English Dictionary in MS Excel format
    By waqaszhr in forum Excel General
    Replies: 0
    Last Post: 12-03-2012, 03:19 PM
  3. Auto fill in text on condition format
    By choy96 in forum Excel General
    Replies: 4
    Last Post: 12-20-2010, 11:48 AM
  4. auto fill custom format numbers
    By NDBC in forum Excel General
    Replies: 2
    Last Post: 10-15-2009, 02:26 AM
  5. [SOLVED] I want to format date cells for the 15th of every month..
    By Maggie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2006, 12:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1