How to calculate with formula results after formatting with =TEXT

[VickiMc]

Hello Experts,
Can someone please put me out of my misery - at the moment I am unable to
see the forest for the trees?

My ultimate aim is to get formula 1b & 2b to work together so that I get to
text results, at the moment I can only get the combinations of 1a & 2b or 1b
& 2a, which give one of each of text & date serial number. (I had it working
once a couple of days ago, but forgot to save the file & now I've gone
completely blank.)

(1a) =IF(AM3=0,"",VLOOKUP(AM3,Break_Dates,2,FALSE))
(1b) =TEXT(IF(AM3=0,"",VLOOKUP(AM3,Break_Dates,2,FALSE)),"ddmmyy")
and
(2a) =IF(AM3>0,AX3+AM3-1,AX3)
(2b) =IF(AM3>0,TEXT(AX3+AM3-1,"ddmmyy"),TEXT(AX3,"ddmmyy"))

I've also given myself another one whereby I need to subtract hours from one
another and result in a number format.
Eg: 17:00 (5PM) minus 15:00 (3PM) = 0.83333334
What I want is for it to give me =2 (as in 2hrs).

I can hear the men with long-armed white jackets approaching - HELP!

 

[Fred Smith]

"Get formula to work together" doesn't give us much to go on. The formulas
look valid to me. What problem are you having and what do you want to do?

Your second question is easy: You either:
-- format the cell as a Time (ie: h:mm), or
-- multiply the result by 24 [as in =(a2-a1)*24], the number of hours in a
day, and format the cell as General or a number

As an aside, what do you need the text for? Why not just format the cells 1b
and 2b as a Date (ie, ddmmyy)?
As a further aside, you will get better help if you provide the actual cell
address (like F3), as opposed to code like 1a, 1b, etc.

Regards,
Fred.

 

[VickiMc]

Thanks Fred,
My apologies first for the confusion.
The (1a), (1b) etc, doesn't refer to code - I'm merely listing the 2
formulas that I've created one of each with & one of each without the "=TEXT".

These formulas appear on "Sheet1", on 'Sheet2' I perform a VLookup of the
cells on "Sheet1" and I want the result to maintain the ddmmyy format.

Subsequently, the Cells on Sheet2 are copied & pasted several more times,
prior to being transferred to a Template Workbook that is uploaded into a
..DAT File.

In short, I want to be able to perform calculations in the first instance
(Sheet1 formulae) and retain the ddmmyy format throughout the various
sequences therafter.

At the moment all I seem to be able to achieve is date serial numbers when I
copy & Paste.

"Get formula to work together" doesn't give us much to go on. The formulas
look valid to me. What problem are you having and what do you want to do?

Your second question is easy: You either:
-- format the cell as a Time (ie: h:mm), or
-- multiply the result by 24 [as in =(a2-a1)*24], the number of hours in a
day, and format the cell as General or a number

As an aside, what do you need the text for? Why not just format the cells 1b
and 2b as a Date (ie, ddmmyy)?
As a further aside, you will get better help if you provide the actual cell
address (like F3), as opposed to code like 1a, 1b, etc.

Regards,
Fred.

Hello Experts,
Can someone please put me out of my misery - at the moment I am unable to
see the forest for the trees?

My ultimate aim is to get formula 1b & 2b to work together so that I get
to
text results, at the moment I can only get the combinations of 1a & 2b or
1b
& 2a, which give one of each of text & date serial number. (I had it
working
once a couple of days ago, but forgot to save the file & now I've gone
completely blank.)

(1a) =IF(AM3=0,"",VLOOKUP(AM3,Break_Dates,2,FALSE))
(1b) =TEXT(IF(AM3=0,"",VLOOKUP(AM3,Break_Dates,2,FALSE)),"ddmmyy")
and
(2a) =IF(AM3>0,AX3+AM3-1,AX3)
(2b) =IF(AM3>0,TEXT(AX3+AM3-1,"ddmmyy"),TEXT(AX3,"ddmmyy"))

I've also given myself another one whereby I need to subtract hours from
one
another and result in a number format.
Eg: 17:00 (5PM) minus 15:00 (3PM) = 0.83333334
What I want is for it to give me =2 (as in 2hrs).

I can hear the men with long-armed white jackets approaching - HELP!

 

[Fred Smith]

"In short, I want to be able to perform calculations in the first instance
(Sheet1 formulae) and retain the ddmmyy format throughout the various
sequences thereafter."

OK. Shouldn't be any trouble, except for maybe the DAT file.

"At the moment all I seem to be able to achieve is date serial numbers when
I
copy & Paste"

When I copy and paste an Excel cell, it retains the formatting. You'll need
to provide more information about exactly what you are doing in order for us
to help you out.

List the steps that you go through, what result you are getting, and what
you want instead. There certainly will be relatively simple solutions to
your problem.

Regards,
Fred.

Thanks Fred,
My apologies first for the confusion.
The (1a), (1b) etc, doesn't refer to code - I'm merely listing the 2
formulas that I've created one of each with & one of each without the
"=TEXT".

These formulas appear on "Sheet1", on 'Sheet2' I perform a VLookup of the
cells on "Sheet1" and I want the result to maintain the ddmmyy format.

Subsequently, the Cells on Sheet2 are copied & pasted several more times,
prior to being transferred to a Template Workbook that is uploaded into a
.DAT File.

In short, I want to be able to perform calculations in the first instance
(Sheet1 formulae) and retain the ddmmyy format throughout the various
sequences therafter.

At the moment all I seem to be able to achieve is date serial numbers when
I
copy & Paste.

"Get formula to work together" doesn't give us much to go on. The
formulas
look valid to me. What problem are you having and what do you want to do?

Your second question is easy: You either:
-- format the cell as a Time (ie: h:mm), or
-- multiply the result by 24 [as in =(a2-a1)*24], the number of hours in
a
day, and format the cell as General or a number

As an aside, what do you need the text for? Why not just format the cells
1b
and 2b as a Date (ie, ddmmyy)?
As a further aside, you will get better help if you provide the actual
cell
address (like F3), as opposed to code like 1a, 1b, etc.

Regards,
Fred.

Hello Experts,
Can someone please put me out of my misery - at the moment I am unable
to
see the forest for the trees?

My ultimate aim is to get formula 1b & 2b to work together so that I
get
to
text results, at the moment I can only get the combinations of 1a & 2b
or
1b
& 2a, which give one of each of text & date serial number. (I had it
working
once a couple of days ago, but forgot to save the file & now I've gone
completely blank.)

(1a) =IF(AM3=0,"",VLOOKUP(AM3,Break_Dates,2,FALSE))
(1b) =TEXT(IF(AM3=0,"",VLOOKUP(AM3,Break_Dates,2,FALSE)),"ddmmyy")
and
(2a) =IF(AM3>0,AX3+AM3-1,AX3)
(2b) =IF(AM3>0,TEXT(AX3+AM3-1,"ddmmyy"),TEXT(AX3,"ddmmyy"))

I've also given myself another one whereby I need to subtract hours
from
one
another and result in a number format.
Eg: 17:00 (5PM) minus 15:00 (3PM) = 0.83333334
What I want is for it to give me =2 (as in 2hrs).

I can hear the men with long-armed white jackets approaching - HELP!