Excel מאחסן את ערכי התאריך והשעה כמספרים סידוריים בקצה האחורי.
המשמעות היא שבעוד אתה עשוי לראות תא כמו "10 בינואר 2020-2022" או "01/10/2020" בתא, בחלק האחורי, Excel מאחסן את זה כמספר. זה שימושי מכיוון שהוא גם מאפשר למשתמשים להוסיף/לגרוע בקלות תאריך ושעה ב- Excel.
ב- Microsoft Excel עבור Windows, "01 בינואר 1900" מאוחסן כ -1, "02 בינואר 1900" מאוחסן כ -2 וכן הלאה.
להלן שתי העמודות בעלות אותם מספרים, אך בעמודה א 'מוצגים מספרים ובעמודה ב' מוצג התאריך המיוצג על ידי מספר זה.
זו גם הסיבה שלפעמים אתה עשוי לצפות לדייט בתא אך בסופו של דבר תראה מספר. אני רואה את זה קורה כל הזמן כשאני מוריד נתונים ממסדי נתונים או מכלים מבוססי אינטרנט.
זה קורה כאשר פורמט התא מוגדר להציג מספר כמספר במקום תאריך.
אז איך נוכל להמיר מספרים סידוריים אלה לתאריכים?
על זה כל המדריך הזה.
במדריך זה, אראה לך שתי דרכים פשוטות באמת להמיר מספרים סידוריים לתאריכים ב- Excel
הערה: Excel עבור Windows משתמש במערכת התאריכים 1900 (כלומר 1 היה מייצג את 01 בינואר 1900, ואילו Excel for Mac משתמש במערכת התאריכים 1904 (שלגברים 1 זה ייצג את 01 בינואר 1904 ב- Excel ב- Mac)אז בואו נתחיל!
המרת מספרים סידוריים לתאריכים באמצעות עיצוב מספרים
הדרך הקלה ביותר להמיר מספר סידורי תאריך לתאריך היא על ידי שינוי עיצוב התאים המכילים מספרים אלה.
תוכל למצוא כמה מתבניות התאריכים הנפוצות בכרטיסייה דף הבית בסרט
תן לי להראות לך כיצד לעשות זאת.
שימוש באפשרויות תבנית התאריך המובנות ברצועת הכלים
נניח שיש לך מערך נתונים כפי שמוצג להלן, ואתה רוצה להמיר את כל המספרים האלה בעמודה A לתאריך המתאים שהוא מייצג.
להלן השלבים לביצוע פעולה זו:
- בחר את התאים המכילים את המספר שברצונך להמיר לתאריך
- לחץ על הכרטיסייה 'דף הבית'
- בקבוצה 'מספר', לחץ על התפריט הנפתח עיצוב מספרים
- בתפריט הנפתח, בחר באפשרות 'תאריך ארוך' או תאריך קצר '(על סמך איזה פורמט תרצה שהמספרים האלה יהיו)
זהו זה!
השלבים שלעיל ימירו את המספרים לפורמט התאריכים שנבחר.
השלבים שלעיל לא שינו את הערך בתא, רק את אופן הצגתו.
שים לב ש- Excel מרים את עיצוב התאריכים הקצרים בהתבסס על ההגדרה האזורית של המערכת שלך. לדוגמה, אם אתה בארה"ב, תבנית התאריך תהיה MM/DD/YYYY, ואם אתה בבריטניה, תבנית התאריך תהיה DD/MM/YYYY.
זוהי אמנם שיטה מהירה להמיר מספרים סידוריים לתאריכים, אך יש לה מספר מגבלות:
- ישנם רק שני פורמטי תאריכים - תאריך קצר ותאריך ארוך (וגם זה בפורמט ספציפי). לכן, אם אתה רוצה לקבל רק את החודש והשנה ולא את היום, לא תוכל לעשות זאת באמצעות אפשרויות אלה.
- אינך יכול להציג תאריך ושעה באמצעות אפשרויות העיצוב הנפתחות. תוכל לבחור להציג את התאריך או השעה אך לא את שניהם.
לכן, אם אתה זקוק לגמישות רבה יותר ברצונך להציג תאריכים ב- Excel, עליך להשתמש באפשרות עיצוב מספרים מותאמים אישית (בהמשך יפורסם במדריך זה).
לפעמים כאשר אתה ממיר מספר לתאריך ב- Excel, במקום התאריך אתה עשוי לראות כמה סימני חשיש (משהו כמו #####). זה קורה כאשר רוחב העמודה אינו מספיק כדי להכיל את התאריך כולו. במקרה כזה, פשוט הגדל את רוחב העמודה.יצירת תבנית תאריך מותאמת אישית באמצעות תיבת דו -שיח עיצוב מספרים
נניח שיש לך מערך נתונים כפי שמוצג להלן ואתה רוצה להמיר את המספרים הסידוריים בעמודה A לתאריכים בפורמט הבא - 01.01.2020
שים לב שאפשרות זו אינה זמינה כברירת מחדל בשיטת הסרט שכיסינו קודם לכן.
להלן השלבים לביצוע פעולה זו:
- בחר את התאים המכילים את המספר שברצונך להמיר לתאריך
- לחץ על הכרטיסייה 'דף הבית'
- בקבוצת המספרים, לחץ על סמל משגר תיבת הדו -שיח (זהו חץ מוטה קטן בפינה השמאלית התחתונה של הקבוצה)
- בתיבת הדו -שיח 'עיצוב תאים' שנפתחת, ודא שהכרטיסייה 'מספר' נבחרה
- באפשרויות 'קטגוריה' בצד ימין, בחר 'תאריך'
- בחר את העיצוב הרצוי מתיבת 'סוג'.
- לחץ על אישור
השלבים שלעיל ימירו את המספרים לפורמט התאריכים שנבחר.
כפי שאתה יכול לראות, ישנן אפשרויות עיצוב תאריכים נוספות במקרה זה (בהשוואה לתאריך הקצר והארוך שקיבלנו עם הסרט).
ואם אינך מוצא את הפורמט שאתה מחפש, תוכל גם ליצור פורמט תאריך משלך.
לדוגמה, נניח שאני רוצה להציג את התאריך בפורמט הבא - 01/01/2020 (שזו כבר לא אפשרות בתיבת הדו -שיח תאי פורמט).
הנה איך אני יכול לעשות זאת:
- בחר את התאים המכילים את המספרים שברצונך להמיר לתאריך
- לחץ על הכרטיסייה דף הבית
- בקבוצה מספרים, לחץ על סמל משגר תיבת הדו -שיח (זהו חץ הטיה קטן בפינה השמאלית התחתונה של הקבוצה)
- בתיבת הדו -שיח 'עיצוב תאים' שנפתחת, ודא שהכרטיסייה 'מספר' נבחרה
- באפשרויות 'קטגוריה' בצד ימין, בחר 'מותאם אישית'
- הזן בשדה מימין
mm/dd/yyyy
- לחץ על אישור
השלבים שלעיל ישנו את כל המספרים לפורמט המספרים שצוין.
עם פורמט מותאם אישית, אתה מקבל שליטה מלאה וזה מאפשר לך להציג את התאריך בכל פורמט שאתה רוצה. תוכל גם ליצור פורמט שבו הוא מציג את התאריך ואת השעה באותו תא.
לדוגמה, באותה דוגמה, אם ברצונך להציג תאריך ושעה, תוכל להשתמש בפורמט שלהלן:
mm/dd/yyyy hh: mm AM/PM
להלן הטבלה המציגה את קודי פורמט התאריכים בהם תוכל להשתמש:
| קוד עיצוב התאריכים | כיצד הוא מעצב את התאריך |
| M | מציג את החודש כמספר בין 1-12 |
| מ"מ | הצג את החודשים כמספרים דו ספרתיים בין התאריכים 01-12 |
| מממ | מציג את החודש כשלוש אותיות כמו בינואר-דצמבר |
| מממ | מציג את השם המלא של החודשים כמו בינואר-דצמבר |
| ממממממ | מציג את שם החודש האלפבית הראשון כמו ב- J-D |
| ד | מציג ימים 1-31 |
| dd | מציג ימים בתאריכים 01-31 |
| ddd | מציג ימים כמו ראשון-שבת |
| dddd | מציג ימים כאחד-שבת |
| yy | מציג שנים כ- 00-99 |
| yyyy | מציג שנים 1900-9999 |
המרת מספרים סידוריים לתאריכים באמצעות נוסחת TEXT
השיטות המכוסות לעיל פועלות על ידי שינוי עיצוב התאים המכילים את המספרים.
אך במקרים מסוימים, ייתכן שלא תוכל להשתמש בשיטות אלה.
לדוגמה, להלן יש לי מערך נתונים שבו למספרים יש אפוסטרוף מוביל - הממיר מספרים אלה לטקסט.
אם תנסה להשתמש באפשרויות המובנות כדי לשנות את עיצוב התאים עם מערך הנתונים הזה, זה לא היה עושה דבר. הסיבה לכך היא שאקסל אינה רואה אלה כמספרים, ומאחר שתאריכים למספרים, Excel מסרב לרצונך לעצב אותם.
במקרה כזה, תוכל להמיר טקסט זה למספרים ולאחר מכן להשתמש בשיטות העיצוב המכוסות לעיל, או להשתמש בטכניקה אחרת כדי להמיר מספרים סידוריים לתאריכים - באמצעות הפונקציה TEXT.
הפונקציה TEXT לוקחת שני ארגומנטים - המספר שצריך לעצב, וקוד הפורמט.
נניח שיש לך מערך נתונים כפי שמוצג להלן, ואתה רוצה להמיר את כל המספרים ואת העמודה A לתאריכים.
מתחת לנוסחה שיכולה לעשות זאת:
= טקסט (A2, "MM/DD/YYYY")
שים לב שבנוסחה ציינתי את עיצוב התאריכים שיהיה בפורמט MM/DD/YYYY. אם אתה צריך להשתמש בעיצוב אחר, תוכל לציין את הקוד עבורו כארגומנט השני של נוסחת TEXT.
תוכל גם להשתמש בנוסחה זו כדי להציג את התאריך ואת השעה.
לדוגמה, אם אתה רוצה שהתוצאה הסופית תהיה בפורמט - 01/01/2020 2020 12:00, השתמש בנוסחה שלהלן:
= טקסט (A2, "mm/dd/yyyy hh: mm AM/PM")
בנוסחה שלעיל, הוספתי גם את פורמט הזמן כך שאם יש חלקים עשרוניים במספרים, הוא יוצג כזמן בשעות ובדקות.
אם אתה רק רוצה את התאריך (ולא את הנוסחה הבסיסית), המר את הנוסחאות לערכים סטטיים באמצעות הדבק מיוחד.
יתרון אחד גדול בשימוש בפונקציית TEXT להמרת מספרים סידוריים לתאריכים הוא שניתן לשלב את תוצאת הנוסחה TEXT עם נוסחאות אחרות. לדוגמה, תוכל לשלב את התאריך המתקבל עם הטקסט ולהראות משהו כגון - תאריך היעד הוא 01-ינואר 2020 אוֹ המועד האחרון הוא 01-ינואר 2020
הערה: זכור תאריכים ושעה מאוחסנים כמספרים, כאשר כל מספר שלם היה מייצג יום אחד שלם והחלק העשרוני היה מייצג חלק כה גדול של היום. אז 1 פירושו יום שלם אחד ו- 0.5 פירושו חצי יום או 12 שעות.
אז אלו שתי דרכים פשוטות בהן תוכל להשתמש כדי להמיר מספרים סידוריים לתאריכים ב- Microsoft Excel.
אני מקווה שמצאת הדרכה שימושית!
