Getting MM-DD HH:MM out of Julian 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 !
 
Ad

Advertisements

C

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.
 
N

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 ?
 
C

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.
 
Ad

Advertisements

N

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 !
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top