Convert Number to Time

[Steved]

Hello form Steved

In cell A1 I have 1445

I would like to in Cell B1 have 245 not 2:45 please
I would like to in Cell C1 have P if afternoon, A morning.

Ok I receive 1445 as a time. I would like to convert it to
245 P as this is how the Database time is inputed.

Thankyou.

 

[Bob Phillips]

=MOD(INT(A1),1200)&IF(A1>1200," P"," A")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steved]

Bob Thankyou very much.

Question
Can your formula which I am using go into VBA
The reason I put this to you is MOD in Col A and IF in Col
B.

Once again I say thankyou.

 

[Bob Phillips]

Steve,

In principle yes, but I am not sure I understand the bit about ... reason I
put this to you is MOD in Col A and IF in Col B. If you just want the bits
in separate cells, use, assuming original value in F1 say

A1: =MOD(INT(F1),1200)
B1: =IF(F1>1200," P"," A")

VBA seems unnecessary, and believe me I am normmally a VBer<g>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[sTEVED]

Hello Bob from Steved

Bob you have achieved the objective

=MOD(INT(R6),1200)

If 1200 cell is blank if 1210 it will show 10

Could you help in modifying so that between 1200 to 1259
it will show 1259 not 59 as in this example.

Thankyou very much for taking time on this one.

Cheers

 

[Steved]

Hello Bob from Steved

Bob you have achieved the objective

=MOD(INT(R6),1200)

If 1200 cell is blank if 1210 it will show 10

Could you help in modifying so that between 1200 to 1259.

It will show 1259 not 59 as in this example.

Thankyou very much for taking time on this one.

Cheers.

 

[Steved]

Hello from Steved

Please refer to Formula issue

Thankyou.

 

[Bob Phillips]

Hello Steved,

This is an emended version for the hour part. I have also added a special
condition for 2400

=IF(R6=2400,1200,IF(R6<1300,MOD(R6,1300),MOD(R6,1200)))

Here also is amended for the A/P part, as I assume you want A for 1259

=IF(R6<1300,"A","P")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steved]

Thankyou Bob.

-----Original Message-----
Hello Steved,

This is an emended version for the hour part. I have also added a special
condition for 2400

=IF(R6=2400,1200,IF(R6<1300,MOD(R6,1300),MOD(R6,1200)))

Here also is amended for the A/P part, as I assume you want A for 1259

=IF(R6<1300,"A","P")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.