Forum Discussion
Custom Time format
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
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.
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.
