Convert times to 24 hour clock

**hazzaska** · 05-06-2018, 05:11 AM

Hi
I am having trouble converting times, i can get it to work so far but all times with a zero at the end show as 3 digit, is there a way to convert it properly please?

thanks for any help

D

**BadlySpelledBuoy** · 05-06-2018, 05:17 AM

What method are you using to convert these times?
You've posted in the VBA section but the attachment has no VBA and no formulas so we have little idea of what you've tried so far.

BSB

**hazzaska** · 05-06-2018, 05:19 AM

Hi Sorry yes i noticed that but i dont know how to move it to the general excel or delete it to start again.

**BadlySpelledBuoy** · 05-06-2018, 05:23 AM

A moderator will have to move / delete it for you.

In the mean time, can you tell us what you've tried so far and explain your desired outcome?

BSB

**hazzaska** · 05-06-2018, 05:30 AM

I have just used this =(IF(a1="","",IF(a1<10,a1+12,a1))), which works except any time ending in zero show as 3 digits ie 10:30 shows as 10:3

Thanks

**Roel Jongman** · 05-06-2018, 06:11 AM

it seems to work but it does not give you times in excel format.. it just gives you the number 19 if time is 7.00
you will never be able to calculate anything based on these times as 7.15 is not quarter past 7

however if you want a quick fix for looking like time just change your formula to

=(IF(a1="","",IF(a1<10,a1+12.0001,a1+.0001)))
and make sure celformatting only allows 2 decimals.

But if you want to do calculation or present properly with : instead of the decimal point you need a different formula
what you then need to do is convert to real excel time, which means in excel time is always a fraction of 1
f.e 0,0 = 12:00AM 0,5 is 12:00PM and 7PM is ~ 0,791666667

this can be done with this formula

=IF(A2<10,TRUNC(A2+12)/24,A2/24)+MOD(A2,1)*100/60/24

see attached file also..

**WasWodge** · 05-06-2018, 06:42 AM

Cross-posted without a link here

**hazzaska** · 05-06-2018, 08:58 AM

Roel Jongman

Thank you for your help much appreciated

Convert times to 24 hour clock

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Convert times to 24 hour clock

Re: Convert times to 24 hour clock

Re: Convert times to 24 hour clock

Re: Convert times to 24 hour clock

Re: Convert times to 24 hour clock

Re: Convert times to 24 hour clock

Re: Convert times to 24 hour clock

Re: Convert times to 24 hour clock

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] 24-hour clock timesheet help

Subtracting 1 hour from 24 hour clock

Default time from 12 hour clock to 24 hour clock

[SOLVED] Decimal to 24 hour clock please.

Decimal to 24 hour clock please.

Decimal to 24 hour clock please.

[SOLVED] Decimal to 24 hour clock please.

Bookmarks

Bookmarks

Posting Permissions