I have a cell that displays the difference between two times in hours. The "absolute" value of the cell is something like 40194, but when displayed in hours it is 13 (just what I want). I want another cell to be a copy of the 13, but display 13 exactly, rather than the 40194. How do I do this?
All you have to do is refer to that cell ( for example =A1) and then use the format painter to copy the format of cell A1 to the second cell..
Cheers!
Deep Dave
Dates and times in Excel are all just numbers, starting with 1 Jan 1900 as '1' and with times as fractions of a day (6am = 0.25, 12noon = 0.5, etc). If you just refer to that cell (using =A1 or similar), the 'internal' value in Excel will still be a number like 42310.541667 and Excel will calculate using that number.
If you want to calculate using '13' then try this instead:
Formula:
Please Login or Register to view this content.
This will turn the value of the 'hh' from your source cell into the hours in text format - but Excel is clever enough to treat 'text' numbers as numbers, so you can still calculate with them.
Hope that makes sense!
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Thanks Aardigspook, that worked. I do have a new question. I wanted to in effect create a count up timer by subtracting a fixed time ( CNTL + SHIFT + ; ) from the current time ( =NOW() ), but it's not taking into account the date of the original time and is refreshing to 0 hours when 24 hours comes up. Does this make sense? How can I get it to tell me 24, 25, 26 hours, etc...?
To get hours to count beyond 24, you need to use [h] in the cell format instead of just h.
Let's say you have 2 Nov 15 15:12 in A1 and 3 Nov 15 20:00 in A2.
If you use the formula =A2-A1 you'll get the answer 1.2, as the second date/time is 1.2 days later than the first. If you format this as hours, using just h, you'll get the answer 4. If you use [h] you'll get 28. If you use [h]:mm you'll get 28:48.
So, probably all you need to do is a simple subtraction, with the cell formatted using the custom format [h].
Hope that helps.
Yes Aardigspook! That helped a lot! It is now reading 73 instead of 1. One more question though, the cell that I was originally asking about, the one I wanted to display 73 as text, is still displaying it as 1. Not sure why...?
Another way to to copy and then Paste, Keep Source Formatting.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
The same formatting applies to the formula I gave you in post 3, so try using =TEXT(A1,"[hh]") instead of =TEXT(A1,"hh").
Ah! That's it! Thank you so much for all your help! BTW how/where does one become an excel expert like yourself?
You're welcome.
I'm self-taught (like I think most members here are) - using a combination of:
- Excel built-in help files (they're actually very useful),
- online help (for example this page on custom formatting),
- online tutorials (like Microsoft's online trg and downloadable training, as well as non-MS ones) and
- forums like this one.
Most folk have particular areas of expertise - I'm good with formatting, fairly good with formulas but only a learner with VBA.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks