Home

Time formatting in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-623207%22%20slang%3D%22en-US%22%3ETime%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-623207%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20do%20a%20dashboard%20using%20an%20export%20of%20data%20from%20a%2010km%20race.%20The%20Chip%20time%20is%20shown%20in%20minutes%20(elapsed)%20but%20when%20I%20click%20in%20the%20cell%20in%20Excel%20is%20shows%20a%20completely%20different%20format%20%2F%20formula%20(I'm%20not%20sure%20which).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20bring%20the%20file%20into%20Tableau%20it%20shows%20as%20a%20date%20in%20the%20format%20above%20the%20chip%20time%20but%20I'd%20like%20to%20be%20able%20to%20show%20and%20analyse%20the%20chip%20time%20in%20MM%3ASS%20format.%26nbsp%3B%20Picture%20attached.....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-623207%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-624034%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-624034%22%20slang%3D%22en-US%22%3EI%20would%20just%20ignore%20the%20date%20part%20the%20formula%20bar%20displays%2C%20it%20should%20not%20affect%20your%20results%20unless%20you%20are%20doing%20date%20calculations%20as%20well.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-624131%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-624131%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F345668%22%20target%3D%22_blank%22%3E%40CHIZLETT123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20remove%20the%20surplus%2024hrs%20with%20a%20formula%20then%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20MOD(%3C%2FSTRONG%3E%3CEM%3Echiptime%3C%2FEM%3E%3CSTRONG%3E%2C1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewill%20do%20it.%26nbsp%3B%20Otherwise%20change%20the%20number%20formatting%20to%3C%2FP%3E%3CP%3E%3CSTRONG%3Ehh%3Amm%3Ass%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20the%20day%20will%20no%20longer%20show.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-624244%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-624244%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3Band%26nbsp%3B%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22943%22%20target%3D%22_blank%22%3E%40peter%3C%2FA%3E_Bartholomew.%20I've%20attached%20a%20two%20line%20sample%20of%20the%20challenge.%20I%20can't%20ignore%20the%20date%20aspect%20because%20it%20does%20interfere%20with%20the%20MM%3ASS%20format%20when%20I%20try%20and%20analyse%20it%20in%20Tableau.%20The%20formula%20given%20I%20can't%20make%20work.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20you%20can%20look%20at%20the%20attached%20for%202%20mins%20I'd%20be%20grateful.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-624343%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-624343%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F345668%22%20target%3D%22_blank%22%3E%40CHIZLETT123%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfraid%20that%20question%20is%20more%20for%26nbsp%3B%3CSPAN%3ETableau%20community.%20Apply%20General%20format%20to%20the%20time%2C%20you'll%20see%20the%20number.%20In%20Excel%20date%20is%20integer%20part%20of%20the%20number%20starting%20from%2001%20Jan%201900%20(%3D1)%2C%20and%20time%20is%20decimal%20part%20of%20the%20number.%20Thus%20dates%20are%20always%20will%20be%20added%20if%20your%20number%20is%20the%20equivalent%20of%20more%20than%2024%20hours.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-624457%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-624457%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F345668%22%20target%3D%22_blank%22%3E%40CHIZLETT123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20use%20the%20MOD%20formula%20I%20suggested%2C%20it%20will%20strip%20off%20the%20date%20element.%3C%2FP%3E%3CP%3EThen%20copy%20the%20formula%20results%20and%20paste%20values%20back%20over%20the%20original%20data.%3C%2FP%3E%3CP%3ETry%20the%20read%20into%20Tableau%20again%20and%20this%20time%20it%20should%20show%20as%20a%20time%20and%20not%20a%20datetime.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-624878%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-624878%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%2C%20but%20that%20doesn't%20work%20for%20the%20elapsed%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-625854%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-625854%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20rather%20assumed%20elapsed%20time%20would%20work%20correctly%20with%20either%20the%20datetime%20or%20the%20time%2C%20provided%20the%20race%20is%20not%20run%20at%20midnight.%26nbsp%3B%20Something%20that%20might%20require%20more%20careful%20handling%20is%20any%20gun%2Fchip%20calibration%20errors%20that%20could%20be%20positive%20or%20negative.%26nbsp%3B%20I%20am%20also%20puzzled%20by%20a%20timing%20system%20that%20times%20to%20the%20nearest%20minute%3B%20I%20am%20more%20used%20to%201%2F100ths%20of%20a%20second.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-626030%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-626030%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPeter%2C%20maybe%20I%20misunderstood%20something%2C%20but%20if%20you%20have%20Chip%20Time%2033%3A33%3A00%20you%20shall%20show%20it%20in%26nbsp%3B%3CSPAN%3ETableau%26nbsp%3Bas%26nbsp%3B33%3A33%3A00%2C%20not%20as%209%3A33%3A00.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ENot%20related%20to%20this%20task%2C%20I%20checked%20for%20few%20minutes%20Tableau%20community%20forum%20-%20issue%20with%20converting%20Excel%20datetime%20representation%20into%20Tableau%20form%20exists%2C%20but%20I%20didn't%20dig%20details.%20Never%20worked%20with%26nbsp%3BTableau.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-632485%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-632485%22%20slang%3D%22en-US%22%3EI%20see%20the%20problem.%20Because%20the%20time%20is%20more%20than%2024%20hrs%2C%20Excel%20adds%20the%20strange%20date%20part%20in%20the%20formula%20bar.%20Perhaps%20you%20can%20avoid%20the%20problem%20by%20formatting%20the%20time%20column%20as%20decimal%20number%20and%20then%20use%20tabloid%20to%20do%20the%20time%20format%3F%20Like%20the%20others%20I%20don't%20know%20tableau.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-639064%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-639064%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20followed%20all%20of%20the%20tips%20and%20the%20MOD%20formula%20works%20to%20change%20the%20number%20into%20the%20MM%3ASS%20format%20but%20when%20I%20then%20cut%20and%20paste%20it%20into%20another%20column%2C%20it%20reverts%20back%20to%20the%201%2F1%2F1900%20MM%3ASS%3A00%20format%20-%20I'm%20really%20puzzled!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643614%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643614%22%20slang%3D%22en-US%22%3EIn%20Excel%2C%20dates%20are%20expressed%20as%20a%20number%20of%20days%20since%20a%20starting%20date.%20Day%201%20is%20Jan%201%2C%201900.%20Time%20is%20held%20as%20a%20fraction%20of%20a%20day%2C%20so%200.5%20equals%20noon%2C%201%20equals%20midnight.%20Your%20times%20are%20more%20than%2024%20hours%2C%20so%20you%20get%20numbers%20larger%20than%201.0%2C%20which%20-in%20the%20formula%20bar-%20Excel%20translates%20to%20a%20combination%20of%20date%20and%20time%2C%20even%20though%20it%20shows%20up%20in%20the%20cell%20formatted%20as%20hours%3Aminutes.%3C%2FLINGO-BODY%3E
CHIZLETT123
New Contributor

I'm trying to do a dashboard using an export of data from a 10km race. The Chip time is shown in minutes (elapsed) but when I click in the cell in Excel is shows a completely different format / formula (I'm not sure which).

 

When I bring the file into Tableau it shows as a date in the format above the chip time but I'd like to be able to show and analyse the chip time in MM:SS format.  Picture attached.....

 

 

11 Replies
I would just ignore the date part the formula bar displays, it should not affect your results unless you are doing date calculations as well.

@CHIZLETT123 

If you want to remove the surplus 24hrs with a formula then

= MOD(chiptime,1)

will do it.  Otherwise change the number formatting to

hh:mm:ss

and the day will no longer show.

Thanks @Jan Karel Pieterse and @Peter_Bartholomew. I've attached a two line sample of the challenge. I can't ignore the date aspect because it does interfere with the MM:SS format when I try and analyse it in Tableau. The formula given I can't make work.

 

If you can look at the attached for 2 mins I'd be grateful.

 

 

@CHIZLETT123 ,

 

Afraid that question is more for Tableau community. Apply General format to the time, you'll see the number. In Excel date is integer part of the number starting from 01 Jan 1900 (=1), and time is decimal part of the number. Thus dates are always will be added if your number is the equivalent of more than 24 hours.

@CHIZLETT123 

If you use the MOD formula I suggested, it will strip off the date element.

Then copy the formula results and paste values back over the original data.

Try the read into Tableau again and this time it should show as a time and not a datetime.

@Peter Bartholomew , but that doesn't work for the elapsed time.

@Sergei Baklan 

I rather assumed elapsed time would work correctly with either the datetime or the time, provided the race is not run at midnight.  Something that might require more careful handling is any gun/chip calibration errors that could be positive or negative.  I am also puzzled by a timing system that times to the nearest minute; I am more used to 1/100ths of a second.

@Peter Bartholomew ,

 

Peter, maybe I misunderstood something, but if you have Chip Time 33:33:00 you shall show it in Tableau as 33:33:00, not as 9:33:00.

 

Not related to this task, I checked for few minutes Tableau community forum - issue with converting Excel datetime representation into Tableau form exists, but I didn't dig details. Never worked with Tableau.

I see the problem. Because the time is more than 24 hrs, Excel adds the strange date part in the formula bar. Perhaps you can avoid the problem by formatting the time column as decimal number and then use tabloid to do the time format? Like the others I don't know tableau.

I've followed all of the tips and the MOD formula works to change the number into the MM:SS format but when I then cut and paste it into another column, it reverts back to the 1/1/1900 MM:SS:00 format - I'm really puzzled! 

In Excel, dates are expressed as a number of days since a starting date. Day 1 is Jan 1, 1900. Time is held as a fraction of a day, so 0.5 equals noon, 1 equals midnight. Your times are more than 24 hours, so you get numbers larger than 1.0, which -in the formula bar- Excel translates to a combination of date and time, even though it shows up in the cell formatted as hours:minutes.
Related Conversations