Convert Date to Serial Date?

[DTTODGG]

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!

[Bob Phillips]

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" <DTTODGG@discussions.microsoft.com> wrote in message
news:1BD21F3A-481B-40AB-8DA6-7FDA6B608A00@microsoft.com...
> 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!

[Daniel CHEN]

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" <DTTODGG@discussions.microsoft.com> wrote in message
news:1BD21F3A-481B-40AB-8DA6-7FDA6B608A00@microsoft.com...
> 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!

[Rayo K]

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!

[DTTODGG]

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!

[Bob Phillips]

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" <DTTODGG@discussions.microsoft.com> wrote in message
news:A594C98F-880B-4830-80E7-1B7C37D8031A@microsoft.com...
> 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!