![]() |
How to calculate with formula results after formatting with =TEXT
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(AM30,AX3+AM3-1,AX3) (2b) =IF(AM30,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! |
How to calculate with formula results after formatting with =TEXT
"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" wrote in message ... 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(AM30,AX3+AM3-1,AX3) (2b) =IF(AM30,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! |
How to calculate with formula results after formatting with =T
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. "Fred Smith" wrote: "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" wrote in message ... 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(AM30,AX3+AM3-1,AX3) (2b) =IF(AM30,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! |
How to calculate with formula results after formatting with =T
"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. "VickiMc" wrote in message ... 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. "Fred Smith" wrote: "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" wrote in message ... 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(AM30,AX3+AM3-1,AX3) (2b) =IF(AM30,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! |
All times are GMT +1. The time now is 10:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com