Custom Time format

Copper Contributor

How do I make a time format including days and miliseconds like dd:hh:mm:ss:mls so I can use formulas to add and subtract times in this format? cause I have a bunch of data using this format that I want to use formulas to add or subtract. How do I make this into a format that I can use?

 

as a side topic... I attached a picture of another glitch that this site is showing for no reason... Microsoft stuff doesn't really seem to be completely finished in any iteration... it's like their bosses just can't give extra time for deadlines or something... like ever XD so they just keep putting out stuff that's not entirely tested and rounded. So I had some trouble posting even this question on a very basic forum site run by... #youguessedit Microsoft lol hahaha

8 Replies

Hi Morgan,

 

It doesn't matter in which format you show your date/time, on behind in any case will be some number which you may add/subtract. Just take into account days are integers starts from 01 Jan, 1990 and time are decimals, e.g. 1 hour = 1/24. Thus your date/time

2018-10-04 20:40:35.120

formatted as

yyyy-mm-dd hh:mm:ss.000

in General format will show

43377.8615175926

nope. I've got no choice. dd:hh:mm:ss:mls is the exact format of the time data... and that's not gonna work because it doesn't. what do I do? Cause I've tried that

Are your date/time cells text or numbers? If numbers there is no such format as dd:hh:mm:ss:mls, that could be dd:hh:mm:ss.000. If text - when you need to transform it to numbers to add/substract.

Could you please attach some sample of what do you have?

 

Example with numbers formatted in date/time is here.

See? I need to add subtract this form of data, which excel doesn't do because this time format isn't in the program... at least not yet. I need to figure out how to make that format of math to work. 

They're not aski if that's what you mean. they're just numbers. Isn't there a way to make a custom format so I could do that though? I've seen you can make new formats.

Again, format itself doesn't do any math. You have number or you have text. If number, you may do any math and present result in any suitable format, date/time in particular. If you have text you need to transform it to number to do the math.

ok then it's a number, so the math part is the messed up part? how would I even know if it's an aski form of number that's actually text?

Morgan, if that is in A1 try to check what will be returned by =ISNUMBER(A1) or =ISTEXT(A1)