Need a custom time format to include Julian Day of Year jjj:hh:mm:ss

I have a very specific need that has recently arisen from a new data collection device. I need to make the following format a 'custom' format that can be easily manipulated across spreadsheets and users.

Specifically, if given any date/time field, say for example 02/27/2014 08:50:37 (on a 24 hour clock), I need to change the format to:

jjj:hh:mm:ss, where jjj is the Julian Day of Year (1-365 or 1-366).

Yes, I have a formula that will work if the date/time is in another cell:

=TEXT(INT(A1)-DATE(YEAR(A1),1,0),"000")&":"&TEXT(A1,"hh:mm:ss")

However, I need to be able to easily manipulate this date/time with simple formulas. For example, if I want to add ten seconds to the time, I need to do a simple formula such as =A1+time(0,0,10), where A1 is my jjj:hh:mm:ss formatted time as mentioned above.

Is this even possible?

Perhaps this thread may be of help?

http://www.excelforum.com/excel-prog...date-time.html

Alf

Thanks for the reply. I saw that thread, but the problems are different. That user has data/time reported in Julian and wants to change it to something more normal. It also still requires the date and time to be in another field, then uses a formula to manipulate that date and time to be displayed in the requested format.

I can already do that.

I need to add a custom data type in the Format Cells dialog do display the data as I mentioned in my original post, so that the date and time can be manipulated easily by Excel novices.

I don't want to change the format of the date and time, I want it to be recognized by Excel as a valid date/time format.

I understand that Excel stores all dates/times as integers. So it seems to me that I should be able to display this integer in any reasonable format.

So whether the date is displayed as:

2/27/2016 14:44:32
or
42427.6142592593
or
058:14:44:32

It is all the same thing.

If my data is displayed as 058:14:44:32, then I can use simple time and date addition formulas to manipulate it. For the types of records that we have, I need to be able to use the 058:14:44:32 format, that is, if it can be done in Excel.