Formula to Convert date

LearnMeExcel

Well-known Member
Joined
Aug 11, 2009
Messages
746
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
hi
how i can convert 6:15 to 6.25 and 18:15 to 6:25
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
If your using them in caluculations, the top formula will need to be incorporated if your adding or subtracting hours, but this works for the top one and the 18:25 will merley need formatted as time and select the format that shows your desire output

Excel Workbook
EF
1206:256.25
1318:256:25 PM
Sheet1
 
Upvote 0
ADD 10/1440 (or 1/144)

Which equates to 10 minutes (there being 1440 minutes in a day)

=A1 + 1/144
 
Upvote 0
Maybe I misunderstood your requirement, is it decimal times you require, ie 6:30 displayed as 6.50 etc
 
Upvote 0
thanx all
Richard
i did as u said
Excel Workbook
ABCDEF
1RichardWeaverWeaverAlphathe Correct result
206:156.2506:2506:2506:25 6.25
318:1518.2518:2518:2506:25 6.25
402:302.502:4002:4002:25 2.5
514:3014.514:4014:4002:25 2.5
Sheet1
Excel 2010
Cell Formulas
RangeFormula
B2=A2*24
B3=A3*24
B4=A4*24
B5=A5*24
C2=A2+1/144
C3=A3+1/144
C4=A4+1/144
C5=A5+1/144
D2=A2+10/1440
D3=A3+10/1440
D4=A4+10/1440
D5=A5+10/1440
E2=MOD(A2,0.5)-(MOD(A2,1/24)-("00:25:00"))
E3=MOD(A3,0.5)-(MOD(A3,1/24)-("00:25:00"))
E4=MOD(A4,0.5)-(MOD(A4,1/24)-("00:25:00"))
E5=MOD(A5,0.5)-(MOD(A5,1/24)-("00:25:00"))


the result from 18:15 is 18.25 not 6.25 i want too ignore 24 system
in column F the Result which i am trying to get

thanx for all
 
Upvote 0
THANX ALPHA
that is what i am looking for
but please could you explains why you use the mod on 0.5
thanx
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top