I have date and time in five columns as follows:

Month, Day, AM Hr, PM Hr, Minute.
4, 25, , 4, 30

I can change to 24 hr system by adding 12 to PM Hr.

How can I change that to a date:time format like
4/25:16:30

mollwollfumble said:

I have date and time in five columns as follows:

Month, Day, AM Hr, PM Hr, Minute.
4, 25, , 4, 30

I can change to 24 hr system by adding 12 to PM Hr.

How can I change that to a date:time format like
4/25:16:30

With data in columns A to E
=A6&”/”&B6&”:”&(C6+D6+IF(D6>0,12,0))&”:”&E6

4/25:16:30

The Rev Dodgson said:

mollwollfumble said:

I have date and time in five columns as follows:

Month, Day, AM Hr, PM Hr, Minute.
4, 25, , 4, 30

I can change to 24 hr system by adding 12 to PM Hr.

How can I change that to a date:time format like
4/25:16:30

With data in columns A to E
=A6&”/”&B6&”:”&(C6+D6+IF(D6>0,12,0))&”:”&E6

4/25:16:30

Thanks rev!
I think I found a different answer here, using custom format d/mm:h:mm
=(C4+IF(D4>0,D4+12,0))/24+E4/(24*60)+B4+(A4-1)*30+1

But yours is nicer.

mollwollfumble said:

The Rev Dodgson said:

mollwollfumble said:

I have date and time in five columns as follows:

Month, Day, AM Hr, PM Hr, Minute.
4, 25, , 4, 30

I can change to 24 hr system by adding 12 to PM Hr.

How can I change that to a date:time format like
4/25:16:30

With data in columns A to E
=A6&”/”&B6&”:”&(C6+D6+IF(D6>0,12,0))&”:”&E6

4/25:16:30

Thanks rev!
I think I found a different answer here, using custom format d/mm:h:mm
=(C4+IF(D4>0,D4+12,0))/24+E4/(24*60)+B4+(A4-1)*30+1

But yours is nicer.

OTOH using a custom format means you have the date as a day number, which can be useful.

It would also be pretty easy to do as a user defined function in VBA.

mollwollfumble said:

The Rev Dodgson said:

mollwollfumble said:

I have date and time in five columns as follows:

Month, Day, AM Hr, PM Hr, Minute.
4, 25, , 4, 30

I can change to 24 hr system by adding 12 to PM Hr.

How can I change that to a date:time format like
4/25:16:30

With data in columns A to E
=A6&”/”&B6&”:”&(C6+D6+IF(D6>0,12,0))&”:”&E6

4/25:16:30

Thanks rev!
I think I found a different answer here, using custom format d/mm:h:mm
=(C4+IF(D4>0,D4+12,0))/24+E4/(24*60)+B4+(A4-1)*30+1

But yours is nicer.

PMSL, I tried your formula in Excel (cut and paste) and it didn’t work … Until I realised that for some odd reason it was writing in white font on a white background! Works perfectly now.

Pain diary is now starting to take shape.

!!

In case you’re wondering when I sleep …

mollwollfumble said:

PMSL, I tried your formula in Excel (cut and paste) and it didn’t work … Until I realised that for some odd reason it was writing in white font on a white background! Works perfectly now.

If you do a straight cut and paste from here you also paste the font and text colour, but not background colour, so you end up with white on white.

You have to paste-special and select text only.

The Rev Dodgson said:

mollwollfumble said:

PMSL, I tried your formula in Excel (cut and paste) and it didn’t work … Until I realised that for some odd reason it was writing in white font on a white background! Works perfectly now.

If you do a straight cut and paste from here you also paste the font and text colour, but not background colour, so you end up with white on white.

You have to paste-special and select text only.

Where’s the Any key?

• You have to paste-special and select text only.

Or paste it directly in the, what do you call it, formula bar at the top…

furious said:

• You have to paste-special and select text only.

Or paste it directly in the, what do you call it, formula bar at the top…

Yeah, that would be the quickest way.