Excel Magic Trick 1076: Convert Date-Time Values to Serial Numbers w TEXT & Custom Number Format

preview_player
Показать описание

See how to Convert Date-Time Values in the form YYYYMMDDHHMMSS to proper date time Serial Numbers that Excel understands using TEXT function & Custom Number Formatting: 0000-00-00 00\:00\:00
1) TEXT converts value to text
2) Custom Number Format: "0000-00-00 00\:00\:00"
3) Math operation converts Text Number back to Number
4) Custom Number Format Cell to display as desired

Tricks learned from barry houdini @ Mr Excel Message Board and Leonid Koyfman @ YouTube

Reference videos:
Excel Magic Trick 721: Convert Text Time Numbers Without A Colon To Time 0655 into 06:55 AM
Excel Magic Trick 1058: Convert YYYYMMDD to Serial Number Date with =TEXT(A1,"0000-00-00")
  1. excelisfun
Рекомендации по теме
Excel Magic Trick 0:03:36

Excel Magic Trick 1076: Convert Date-Time Values to Serial Numbers w TEXT & Custom Number Format

Excel Magic Trick 0:03:47

Excel Magic Trick 1074: Multiply Quantity Times Debit or Credit: 3 Formulas Examples.

Excel Magic Trick 0:04:01

Excel Magic Trick 1019: Formula For Text Month Labels From Serial Date: EDATE, TEXT & COLUMNS

Excel Magic Trick 0:03:10

Excel Magic Trick 1058 Convert YYYYMMDD to Serial Number Date with =TEXTA1,0000 00 00

Excel Magic Trick 0:03:21

Excel Magic Trick 1077: Floating Cell In Excel? Watch Window to See Formula Anywhere in Workbook

Excel Magic Trick 0:01:46

Excel Magic Trick 1146: Create ISO Dates using TEXT function and Custom Number Format

Excel Magic Trick 0:03:10

Excel Magic Trick 1058: Convert YYYYMMDD to Serial Number Date with =TEXT(A1,'0000-00-00')

Excel Magic Trick 0:07:41

Excel Magic Trick 1075: Allocate Hours Across Workdays For Each Month Between Start & End Date

Excel Magic Trick 0:10:35

Excel Magic Trick 1072: Conditionally Format Partial Row Based On Start Month, End Month & Produ...

Excel Magic Trick 0:09:47

Excel Magic Trick 1196: YYMMDD Text Dates To Serial Number Dates? 2 More Formulas & Other Tips…...

Excel Magic Trick 0:09:54

Excel Magic Trick 1144 Top 3 TEXT Function uses ISO Date to Date , Text Time To Time , Date To Month

Excel Magic Trick 0:12:45

Excel Magic Trick 1056 Excel 2013 Slicer Formulas (nowtelugu.com)

Excel Magic Trick 0:05:50

Excel Magic Trick 1078: Total Cost Based On Per Minute Charge & Excel Time Values Nearest 15 Sec...

Excel Magic Trick 0:02:33

Excel Magic Trick 1060 Double Click To Copy Formulas Down Column, 5 Examples Some Empty Cells

Excel Magic Trick 0:04:01

Excel Magic Trick 1019 Formula For Text Month Labels From Serial Date EDATE, TEXT & COLUMNS

Converting Time Values 0:08:31

Converting Time Values for Biliing

Excel Magic Trick 0:07:14

Excel Magic Trick 1236: Count Numbers that Contain Whole Number 4: COUNTIFS or SUMPRODUCT?

Excel 2013 Convert 0:01:01

Excel 2013 Convert Dates using Excel's Text to Column Wizard

Excel Magic Trick 0:06:51

Excel Magic Trick 1065 SUMIFS Month Over Month Sales Calculations From Transactional Data Set

Convert date into 0:00:56

Convert date into number(1 minute video)

How to Convert 0:01:54

How to Convert Unix Date to Excel Date

Stop converting numbers 0:01:27

Stop converting numbers to date in Excel cells

Converting Nondates to 0:04:11

Converting Nondates to Dates

Thingspeak [2] : 0:02:02

Thingspeak [2] : export data to excel and convert UTC to GMT+7

Комментарии
Автор

Excel Magic Trick 1076: Convert Date-Time Values to Serial Numbers w TEXT & Custom Number Format 

excelisfun
Автор

Dear Mike, amazing tricks to converting date. It is really helpful, it made my job very easy.

khanasifkhan
Автор

Olá Mike, mais um vídeo excelente..obrigado.

Luciano_mp
Автор

Mike, thanks a million. Excell is fun! Im gonna review other videos of yours to improve this worksheet with additional stats. By the way, can you explain why in your formula the #1440 was used? Thanks.

chowman
Автор

Hello thank you. If you want to convert a date into the serial number what function would you use. the context is that want to concatenate the date value as part of a string as a unique code for a vlookup
Thank you.

londontownfc
Автор

folk it was great but exists a lot of diffirent ways to do dates and i cant figure out which is for each problem..do y have a video with all dates tranformations ways?

oddnumber
Автор

Hi Mike, you amaze me. Like the video. Ive posted a post concerning the calculating of times. Ive checked some of your other videos but I'm stuck in two areas. For example, the sheet I created logs calls, when they start n when they end. Can I format the cell to read only in total minutes? Ie, 1:22:34 to read 82:34 mins. I then need to calculate the cost, lets say .24 cents per minute. If my math is correct this call cost me $19.76 @ .24 pm...right? Hope u can help or tell me what trick can aide me on this problem. If you like to consider this problem on a feature video, consider the following issues...time n date stamps for the start n end of a call, total hours n minutes, cost n filtering the report by date. I know that I can use the built in filter function in excel. Again, thanks n keep those videos coming. God bless.

chowman
Автор

Is there any way to make some encryption also? like to embed the current date and time into serial number but encrypted. Would be interesting.

dimitrikolotauri
Автор

Nice video, can i replicate in Portuguese?

simpleConciliation
Автор

Hello ExcellsFun, I have an format of April 14, 2015, 12:00:00 AM
Which I need to change to normal dd-mm-yyyy hh:mm:ss . Please let me know how to do it

InvincibleDR
Автор

I need your help Sir.

How can I split this format 6/23/2014  6:41:08 PM  to this format 6:41:08 PM

I'll be really grateful for your help.

ahmedjamalhumakani