| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert Text to DATE | Excel Discussion (Misc queries) | |||
| Is there a macro to convert times between 2 places dep. on date? | Excel Worksheet Functions | |||
| convert interval to various separate date , time, hr, minutes | Excel Worksheet Functions | |||
| how do I convert a UK date to a US date in excel? | Excel Discussion (Misc queries) | |||
| Convert date to length of time in months from set date | Excel Worksheet Functions | |||
Hybrid Mode
