Getting MM-DD HH:MM out of Julian date

  • Thread starter Thread starter Nick W
  • Start date Start date
N

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 !
 
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.
Click to expand...

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


Regards
Claus B.
 
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 ?
 
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 ?
Click to expand...

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.
 
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 !
 
Back
Top