Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default Conditional Formatting (Date vs Number)

I have a spreadsheet that is calculating intervals for equipment
maintenance. Some of the equipment must be serviced every x months,
some of the equipment must be serviced every x hours (of run time).

I have all the calculations worked out. The problem I am having is with
conditional formatting. In column A I have text that says either
"HOURS" or "MONTHS". Based on the value of that cell, I need Column B
to be formatted as either a Date (to return the date of the next
service) or as a Number (to return the hours till next service).

Any ideas on how to do that? Conditional formatting doesnt give the
option to format anything except Font, Border and Patterns. I need to
format the data type.

Thanks,

Robbie

  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
if you need this automatic you'll have to use VBA. You may try the following
addin (which allows you this functionality):
http://www.xldynamic.com/source/xld.....Download.html

--
Regards
Frank Kabel
Frankfurt, Germany
schrieb im Newsbeitrag
oups.com...
I have a spreadsheet that is calculating intervals for equipment
maintenance. Some of the equipment must be serviced every x months,
some of the equipment must be serviced every x hours (of run time).

I have all the calculations worked out. The problem I am having is with
conditional formatting. In column A I have text that says either
"HOURS" or "MONTHS". Based on the value of that cell, I need Column B
to be formatted as either a Date (to return the date of the next
service) or as a Number (to return the hours till next service).

Any ideas on how to do that? Conditional formatting doesnt give the
option to format anything except Font, Border and Patterns. I need to
format the data type.

Thanks,

Robbie



  #3   Report Post  
 
Posts: n/a
Default

I will probably have to use the VBA code. The spreadsheet will need to
be distributed to other clients and I dont want to have to add a
plug-in. (Can the plug-in be attached to a sheet to "go along with it")
The thing about using code that I'm confused about is how to make it so
that it applies to all cells in a column. I want to be able to insert
cells and add records randomly and those inserted cells need to carry
the same format.

I apologize if that is a simple process. I am just a couple of rungs
above clueless when it comes to VBA.

Thanks again,

Robbie

  #4   Report Post  
 
Posts: n/a
Default

I will probably have to use the VBA code. The spreadsheet will need to
be distributed to other clients and I dont want to have to add a
plug-in. (Can the plug-in be attached to a sheet to "go along with it")
The thing about using code that I'm confused about is how to make it so
that it applies to all cells in a column. I want to be able to insert
cells and add records randomly and those inserted cells need to carry
the same format.

I apologize if that is a simple process. I am just a couple of rungs
above clueless when it comes to VBA.

Thanks again,

Robbie

  #5   Report Post  
 
Posts: n/a
Default

I will probably have to use the VBA code. The spreadsheet will need to
be distributed to other clients and I dont want to have to add a
plug-in. (Can the plug-in be attached to a sheet to "go along with it")
The thing about using code that I'm confused about is how to make it so
that it applies to all cells in a column. I want to be able to insert
cells and add records randomly and those inserted cells need to carry
the same format.

I apologize if that is a simple process. I am just a couple of rungs
above clueless when it comes to VBA.

Thanks again,

Robbie



  #6   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
you can do this with a worksheet_caculate or worksheet_change procedure on
your won (see: http://www.cpearson.com/excel/events.htm). But these will
probably slow down your spreadsheet significantly (if you have many rows to
process)

If this goes to other clients you may consider changing your spreadsheet
layout a little bit or using a userform four your data entries instead

--
Regards
Frank Kabel
Frankfurt, Germany
schrieb im Newsbeitrag
oups.com...
I will probably have to use the VBA code. The spreadsheet will need to
be distributed to other clients and I dont want to have to add a
plug-in. (Can the plug-in be attached to a sheet to "go along with it")
The thing about using code that I'm confused about is how to make it so
that it applies to all cells in a column. I want to be able to insert
cells and add records randomly and those inserted cells need to carry
the same format.

I apologize if that is a simple process. I am just a couple of rungs
above clueless when it comes to VBA.

Thanks again,

Robbie



  #7   Report Post  
 
Posts: n/a
Default

I will probably have to use the VBA code. The spreadsheet will need to
be distributed to other clients and I dont want to have to add a
plug-in. (Can the plug-in be attached to a sheet to "go along with it")
The thing about using code that I'm confused about is how to make it so
that it applies to all cells in a column. I want to be able to insert
cells and add records randomly and those inserted cells need to carry
the same format.

I apologize if that is a simple process. I am just a couple of rungs
above clueless when it comes to VBA.

Thanks again,

Robbie

  #8   Report Post  
 
Posts: n/a
Default

This works. Thanks for your help.

Sub FormatNextDue()

Dim Cell As Range

For Each Cell In Intersect(ActiveSheet.UsedRange, Columns("C:C"))
If Cell.Value = "HOURS" Then Cell.Offset(0, 3).NumberFormat = "0.0"
If Cell.Value = "MONTHS" Then Cell.Offset(0, 3).NumberFormat =
"mmm-yy;@"
Next Cell

End Sub

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formatting in Charts? Todd Charts and Charting in Excel 1 January 11th 05 06:32 PM
Use Julian Date To Create Serial Number antho10359 Excel Discussion (Misc queries) 4 December 9th 04 01:50 AM
conditional formatting blank cell TREK5200 Excel Discussion (Misc queries) 1 December 6th 04 02:23 AM
Date Formatting Neil Excel Discussion (Misc queries) 6 December 4th 04 06:57 PM
Adding more than three Conditions to 'Conditional Formatting' David McRitchie Excel Discussion (Misc queries) 1 November 27th 04 06:03 PM


All times are GMT +1. The time now is 09:07 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"