Convert Date to Serial Date?
 

Hello-
I have an excel file sent to me everyday.
Col G is formatted as "DATE" not general (2/8/2006)
Col H is a money field.

Col I - I want to find out how much money/day (formatted as "currency".

Take "today" - Col G and divide by Col H put in Col I.

The problem is Col G is not a "general" field. I suppose I could everyday
change Col G to "General", but then people can't figure out what date 37344
is.

Is there a clean way to do this?
Thank you so much - I hate working with dates in excel!

Convert Date to Serial Date?
 

Why would you divide a money by a date? That will be dividing by 37344 for
example which seems a bit arbitrary.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DTTODGG" wrote in message
...
Hello-
I have an excel file sent to me everyday.
Col G is formatted as "DATE" not general (2/8/2006)
Col H is a money field.

Col I - I want to find out how much money/day (formatted as "currency".

Take "today" - Col G and divide by Col H put in Col I.

The problem is Col G is not a "general" field. I suppose I could everyday
change Col G to "General", but then people can't figure out what date
37344
is.

Is there a clean way to do this?
Thank you so much - I hate working with dates in excel!

Convert Date to Serial Date?
 

How about try this:

In column I, type formula like =H1/TEXT(G1,"0").
Format column I as currency. Keep column G as date.

In my computer, you even do not need to use Text function.
The following is from my computer:
Column A = today() and format as Friday, April 21, 2006
Columb B = 5000 and format as $5000.00
Column C = B1/A1 and format as currency.
In my column C, it shows $0.13.

I am not quite whether this is what you want.

--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Downloand
=================================

"DTTODGG" wrote in message
...
Hello-
I have an excel file sent to me everyday.
Col G is formatted as "DATE" not general (2/8/2006)
Col H is a money field.

Col I - I want to find out how much money/day (formatted as "currency".

Take "today" - Col G and divide by Col H put in Col I.

The problem is Col G is not a "general" field. I suppose I could everyday
change Col G to "General", but then people can't figure out what date
37344
is.

Is there a clean way to do this?
Thank you so much - I hate working with dates in excel!

Convert Date to Serial Date?
 

What does the money field represent? Anyway if you want money per day,
wouldn't you want to divide by a specfic number of days vice the actual date
itself?

"DTTODGG" wrote:

Hello-
I have an excel file sent to me everyday.
Col G is formatted as "DATE" not general (2/8/2006)
Col H is a money field.

Col I - I want to find out how much money/day (formatted as "currency".

Take "today" - Col G and divide by Col H put in Col I.

The problem is Col G is not a "general" field. I suppose I could everyday
change Col G to "General", but then people can't figure out what date 37344
is.

Is there a clean way to do this?
Thank you so much - I hate working with dates in excel!

Convert Date to Serial Date?
 

Sorry to be so unclear, I'll try again.

First I need to find out how many days there are between today and Col G.
Then I need to divide (yes, I flipped them incorrectly below) to find out
how many dollars/day are being made.

Row 2 has a date of 03/30/2006 and $1662.25 = 1662.75/(37366-37344) =
$75.56/day

Row 3 has a date of 04/11/2006 and $70.00 = 70.00/(37366-37356) = $7.00/day

It seems so simple, it's like the reverse of "TEXT", how to convert a date
to serial while in a date formatted cell.

Thank you so much for responding.


"DTTODGG" wrote:

Hello-
I have an excel file sent to me everyday.
Col G is formatted as "DATE" not general (2/8/2006)
Col H is a money field.

Col I - I want to find out how much money/day (formatted as "currency".

Take "today" - Col G and divide by Col H put in Col I.

The problem is Col G is not a "general" field. I suppose I could everyday
change Col G to "General", but then people can't figure out what date 37344
is.

Is there a clean way to do this?
Thank you so much - I hate working with dates in excel!

Convert Date to Serial Date?
 

Okay, that is much better.

Assuming date in A2, money in B2, try

=B2/(TODAY()-A2)

and format as currency.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DTTODGG" wrote in message
...
Sorry to be so unclear, I'll try again.

First I need to find out how many days there are between today and Col G.
Then I need to divide (yes, I flipped them incorrectly below) to find out
how many dollars/day are being made.

Row 2 has a date of 03/30/2006 and $1662.25 = 1662.75/(37366-37344) =
$75.56/day

Row 3 has a date of 04/11/2006 and $70.00 = 70.00/(37366-37356) =
$7.00/day

It seems so simple, it's like the reverse of "TEXT", how to convert a date
to serial while in a date formatted cell.

Thank you so much for responding.


"DTTODGG" wrote:

Hello-
I have an excel file sent to me everyday.
Col G is formatted as "DATE" not general (2/8/2006)
Col H is a money field.

Col I - I want to find out how much money/day (formatted as "currency".

Take "today" - Col G and divide by Col H put in Col I.

The problem is Col G is not a "general" field. I suppose I could
everyday
change Col G to "General", but then people can't figure out what date
37344
is.

Is there a clean way to do this?
Thank you so much - I hate working with dates in excel!