Getting MM-DD HH:MM out of Julian date

[Nick W]

I have an auto populated file with lots of columns, where all dates are Julian.

Entering =TEXT(D18-C18,"mm-dd hh:mm") into column E adds a month onto every result ? e.g. below...

Row C D E
22:17:16 2012/05/29 23:30:00 2012/05/29 01-00 01:12

Note the formatting for ease of reading for column C & D is set to hh:mm:ss yyyy/mm/dd.

What am I missing that is adding a month onto every result ?

Please be very gentle, I'm a total novice and haven't a clue about VBA or how to do it despite many years of hitting my head hard against anything I can whilst attempting it !

 

[Claus Busch]

Hi Nick,

Am Thu, 9 Oct 2014 04:26:14 -0700 (PDT) schrieb Nick W:

Row C D E
22:17:16 2012/05/29 23:30:00 2012/05/29 01-00 01:12

Note the formatting for ease of reading for column C & D is set to hh:mm:ss yyyy/mm/dd.

your timestamp in C1 then:
=EDATE(INT(C1),1)+MOD(C1,1)
to add a month


Regards
Claus B.

 

[Nick W]

Sorry Claus, but I don't understand.
I don't want to add a month but subtract one from the result in column E - and I can't understand why it's doing it ?

 

[Claus Busch]

Hi Nick,

Am Thu, 9 Oct 2014 04:45:01 -0700 (PDT) schrieb Nick W:

Sorry Claus, but I don't understand.
I don't want to add a month but subtract one from the result in column E - and I can't understand why it's doing it ?

if you substract D18-C18 the result is 01:12:44 hours
and 0 days, 0 month and 0 years.
And day 0 in Excel date management is 31.12.1899 and that is with your
format the 0. day of January 1900. And that is why you get month 1.


Regards
Claus B.

 

[Nick W]

Oh, that answers that thank you, so I have to do my maths and subtract one month from all answers by adding in your previously sent formula... ?

Oh I hate Excel ! There's no common sense behind it !