Julian day with time conversion

jracela

New Member
Say you have YEAR (2008) in Column B, Julian Day of Year in Column C (61=March 1 in a leap year), and Hour Minute in Column D (1627=04:27 PM). What formula will compute the combined Date/Time for these cells and output in Column E as 3/1/08 16:27?

TIA
Jay

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Norie

Well-known Member
Jay

Something like this perhaps.

=DATE(B1,1,0)+C1+TIMEVALUE(LEFT(D1,2)&":"&RIGHT(D1,2))

Though I'm sure there's probably a better, more correct way.

Andrew Poulsom

MrExcel MVP
An alternative:

=DATE(B1,1,C1)+TEXT(D1,"00\:00")

jracela

New Member
Works perfect - Thank you!

I have another question but will start a new thread with it...

Replies
3
Views
343
Replies
1
Views
39
Replies
7
Views
70
Replies
2
Views
117
Replies
3
Views
369

1,191,576
Messages
5,987,390
Members
440,095
Latest member
yanaungmyint

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back