Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date in merged cells
Have the date in the format of Month xx, 2006 in a set of six merged cells.
In another set of six merged cells I have a later date. Is in not possible to extract the difference in days into a third set of merged cells. Works fine and displays nicely if the dates are in single wide cells but doesn't seem to work from merged cells. :-( Eventually the sheet will contain about 50 counter readings which are taken 30 to 60 days apart. We need to have it calculate the interval in days and the number of operations per day for each piece of equipment. (I've used narrow column widths since lower down would be boxes where a single digit or check mark can be placed to indicate levels or good or poor status condition). Is there a better way to layout out the spread sheet. TIA |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
date in merged cells
How is it 'not possible" ? What happens with =F1-D1 when both cells are
merged with empty neighbours I was able to do it - I did have to format the cell with the formula to General It even worked when the formula was in a merged cell I use XL2003; what version do you have? best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "labatyd" wrote in message ... Have the date in the format of Month xx, 2006 in a set of six merged cells. In another set of six merged cells I have a later date. Is in not possible to extract the difference in days into a third set of merged cells. Works fine and displays nicely if the dates are in single wide cells but doesn't seem to work from merged cells. :-( Eventually the sheet will contain about 50 counter readings which are taken 30 to 60 days apart. We need to have it calculate the interval in days and the number of operations per day for each piece of equipment. (I've used narrow column widths since lower down would be boxes where a single digit or check mark can be placed to indicate levels or good or poor status condition). Is there a better way to layout out the spread sheet. TIA |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
date in merged cells
Are you sure your dates are formatted correctly? Check under Format - Cells
to make sure they are set to custom format mmmm dd, yyyy. When creating the formula to calculate the date difference you only reference the top left cell of the merged range, e.g. six cells merged across from A1 would not be referenced as A1:F1 but just as A1 so you can use a formula something like G1-A1 to work out the difference between the dates (don't forget to format the result as a number). Andrea Jones "labatyd" wrote: Have the date in the format of Month xx, 2006 in a set of six merged cells. In another set of six merged cells I have a later date. Is in not possible to extract the difference in days into a third set of merged cells. Works fine and displays nicely if the dates are in single wide cells but doesn't seem to work from merged cells. :-( Eventually the sheet will contain about 50 counter readings which are taken 30 to 60 days apart. We need to have it calculate the interval in days and the number of operations per day for each piece of equipment. (I've used narrow column widths since lower down would be boxes where a single digit or check mark can be placed to indicate levels or good or poor status condition). Is there a better way to layout out the spread sheet. TIA |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
date in merged cells
Working with data in merged cells sometimes creates problems. This seems to
be one of those times. I'd recommend un-merging them and then choose the group again but this time use Format | Cells and on the [Alignment] tab, under Horizontal Alignment, choose the 'Center Across Selection' option. Your date will have plenty of room to be displayed and your date difference formulas should work for you. Just do everything in the left-most cell of the group, such as date formatting and as the reference for date computations. "labatyd" wrote: Have the date in the format of Month xx, 2006 in a set of six merged cells. In another set of six merged cells I have a later date. Is in not possible to extract the difference in days into a third set of merged cells. Works fine and displays nicely if the dates are in single wide cells but doesn't seem to work from merged cells. :-( Eventually the sheet will contain about 50 counter readings which are taken 30 to 60 days apart. We need to have it calculate the interval in days and the number of operations per day for each piece of equipment. (I've used narrow column widths since lower down would be boxes where a single digit or check mark can be placed to indicate levels or good or poor status condition). Is there a better way to layout out the spread sheet. TIA |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
date in merged cells
Thanks group. Aaahhhh, so simple. Got it. Works fine now. And looks the way I wanted it. I'll keep plugging away at this project and most likely be back. <big grin "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Working with data in merged cells sometimes creates problems. This seems to be one of those times. I'd recommend un-merging them and then choose the group again but this time use Format | Cells and on the [Alignment] tab, under Horizontal Alignment, choose the 'Center Across Selection' option. Your date will have plenty of room to be displayed and your date difference formulas should work for you. Just do everything in the left-most cell of the group, such as date formatting and as the reference for date computations. "labatyd" wrote: Have the date in the format of Month xx, 2006 in a set of six merged cells. In another set of six merged cells I have a later date. <cut |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Lock date in multiple cells | Excel Worksheet Functions | |||
Blank Rows from Merged Cells in Drop Down Menu | Excel Discussion (Misc queries) | |||
Move merged cells with conditional formatting? | Excel Worksheet Functions | |||
Adding XY days to date in cells | Excel Discussion (Misc queries) | |||
Getting Excel to treat merged cells as one cell when printing | Excel Discussion (Misc queries) |