| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can this be done
I have a worksheet where I enter data on a weekly basis - (car servicing) at
present I have been able to total the distance travelled since the last serviced. What I would like to be able to do is display how much time - (in months, weeks and days) till the next service interval, knowing that each service interval is three months or six months hence. In other words count down the time from previous service to next service. Can someone, firstly tell me if it can be done and secondly how can it be done ?? -- Steven. In God we trust, all others we virus scan. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can this be done
Hi Steven, Assuming your dates are in cells D1 and B1 - ( D1 with oldest date ): =DATEDIF(D1,B1,"Y") & " Years, " & DATEDIF(D1,B1,"YM") & " Months, " & DATEDIF(D1,B1,"MD") & " Days" Formula will give your countdown ... HTH Cheers Carim -- Carim ------------------------------------------------------------------------ Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259 View this thread: http://www.excelforum.com/showthread...hreadid=531287 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can this be done
hi! something like C1: =ROUND(((A1+B1)-TODAY())/7,0)&" week(s) "&MOD((A1+B1)-TODAY(),7)&" day(s) " assuming that the last service date is in A1 and the period of service interval in days in B1. -via135 elusiverunner Wrote: I have a worksheet where I enter data on a weekly basis - (car servicing) at present I have been able to total the distance travelled since the last serviced. What I would like to be able to do is display how much time - (in months, weeks and days) till the next service interval, knowing that each service interval is three months or six months hence. In other words count down the time from previous service to next service. Can someone, firstly tell me if it can be done and secondly how can it be done ?? -- Steven. In God we trust, all others we virus scan. -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=531287 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can this be done
Steven, Just realized your problem deals only with months and days ... = DATEDIF(D1,today(),"YM") & " Months, " & DATEDIF(D1,today(),"MD") & " Days" where D1 is equal to the next service date ... HTH Cheers Carim -- Carim ------------------------------------------------------------------------ Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259 View this thread: http://www.excelforum.com/showthread...hreadid=531287 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can this be done
Assuming the last service date is in A1, the number of months until the next
service date is in B1, enter in C1 =DATE(YEAR(A1),MONTH(A1)+B1,DAY(B1)) to get the next service date and in D1 =DATEDIF(TODAY(),C1,"M")&" Months, "&INT((EOMONTH(TODAY(),0)-TODAY()+DAY(C1))/7)&" Weeks, "&ROUND(MOD((EOMONTH(TODAY(),0)-TODAY()+DAY(C1))/7,1)*7,0)&" Days" to get the number of Months,Weeks,Days. Try it out and see if it gives the results you want. "elusiverunner" wrote: I have a worksheet where I enter data on a weekly basis - (car servicing) at present I have been able to total the distance travelled since the last serviced. What I would like to be able to do is display how much time - (in months, weeks and days) till the next service interval, knowing that each service interval is three months or six months hence. In other words count down the time from previous service to next service. Can someone, firstly tell me if it can be done and secondly how can it be done ?? -- Steven. In God we trust, all others we virus scan. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can this be done
Thank you all for this great help, but I think I need to further detail the
set up. In column A I have the weekly date if the format dd.mm.yyyy at which I enter the speedo reading at the end of the week (normally Saturday); the next column is the speedo reading at the service. There is not much in this column except the reading from the speedo when the car is serviced as at the bottom of the table I show the number of kilometres covered since the last service; in column C I have the speedo reading at the end of the week (normally Saturday). I want to put a cell at the bottom of the table that shows how many weeks and/or days until the car is to be serviced again - normally 91 or 182 days (3 to 6 months). The reason I need to do this is because the car is serviced every 5,000km/or six months, whichever occurs first. Some times the car is serviced by kilometreage sometimes by months depending on how much travelling I do. -- Steven. In God we trust, all others we virus scan. "via135" wrote: hi! something like C1: =ROUND(((A1+B1)-TODAY())/7,0)&" week(s) "&MOD((A1+B1)-TODAY(),7)&" day(s) " assuming that the last service date is in A1 and the period of service interval in days in B1. -via135 elusiverunner Wrote: I have a worksheet where I enter data on a weekly basis - (car servicing) at present I have been able to total the distance travelled since the last serviced. What I would like to be able to do is display how much time - (in months, weeks and days) till the next service interval, knowing that each service interval is three months or six months hence. In other words count down the time from previous service to next service. Can someone, firstly tell me if it can be done and secondly how can it be done ?? -- Steven. In God we trust, all others we virus scan. -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=531287 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can this be done
Never mind, that formula won't work 100% of the time (such as when the
interval is exactly six months). It sounds like you first need to determine what the last service date was. Assuming A1:A20 is your weekly dates, B1:B20 is the mileage readings for your service dates, you could try entering in A22 (format the cell as a date) =SUMPRODUCT(--(B1:B20=MAX(B1:B20)),A1:A20) to find the last service date. In B22 enter the number of months (such as 6). In C22 enter =DATE(YEAR(A22),MONTH(A22)+B22,DAY(A22)) to find the next service date (6 months from the last service date) and (modified after seeing the good suggestions others posted) =DATEDIF(TODAY(),C22,"M")&" Months, "&INT(DATEDIF(TODAY(),C22,"MD")/7)&" Weeks, "&ROUND(MOD(DATEDIF(TODAY(),C22,"MD")/7,1)*7,0)&" Days" to find the number of Months, Weeks, and Days between today's date and the next service date. Change range references as necessary. "JMB" wrote: Assuming the last service date is in A1, the number of months until the next service date is in B1, enter in C1 =DATE(YEAR(A1),MONTH(A1)+B1,DAY(B1)) to get the next service date and in D1 =DATEDIF(TODAY(),C1,"M")&" Months, "&INT((EOMONTH(TODAY(),0)-TODAY()+DAY(C1))/7)&" Weeks, "&ROUND(MOD((EOMONTH(TODAY(),0)-TODAY()+DAY(C1))/7,1)*7,0)&" Days" to get the number of Months,Weeks,Days. Try it out and see if it gives the results you want. "elusiverunner" wrote: I have a worksheet where I enter data on a weekly basis - (car servicing) at present I have been able to total the distance travelled since the last serviced. What I would like to be able to do is display how much time - (in months, weeks and days) till the next service interval, knowing that each service interval is three months or six months hence. In other words count down the time from previous service to next service. Can someone, firstly tell me if it can be done and secondly how can it be done ?? -- Steven. In God we trust, all others we virus scan. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can this be done
Hi JMB
Do you have an email address to which I could send you the table to look at ? -- Steven. In God we trust, all others we virus scan. "JMB" wrote: Never mind, that formula won't work 100% of the time (such as when the interval is exactly six months). It sounds like you first need to determine what the last service date was. Assuming A1:A20 is your weekly dates, B1:B20 is the mileage readings for your service dates, you could try entering in A22 (format the cell as a date) =SUMPRODUCT(--(B1:B20=MAX(B1:B20)),A1:A20) to find the last service date. In B22 enter the number of months (such as 6). In C22 enter =DATE(YEAR(A22),MONTH(A22)+B22,DAY(A22)) to find the next service date (6 months from the last service date) and (modified after seeing the good suggestions others posted) =DATEDIF(TODAY(),C22,"M")&" Months, "&INT(DATEDIF(TODAY(),C22,"MD")/7)&" Weeks, "&ROUND(MOD(DATEDIF(TODAY(),C22,"MD")/7,1)*7,0)&" Days" to find the number of Months, Weeks, and Days between today's date and the next service date. Change range references as necessary. "JMB" wrote: Assuming the last service date is in A1, the number of months until the next service date is in B1, enter in C1 =DATE(YEAR(A1),MONTH(A1)+B1,DAY(B1)) to get the next service date and in D1 =DATEDIF(TODAY(),C1,"M")&" Months, "&INT((EOMONTH(TODAY(),0)-TODAY()+DAY(C1))/7)&" Weeks, "&ROUND(MOD((EOMONTH(TODAY(),0)-TODAY()+DAY(C1))/7,1)*7,0)&" Days" to get the number of Months,Weeks,Days. Try it out and see if it gives the results you want. "elusiverunner" wrote: I have a worksheet where I enter data on a weekly basis - (car servicing) at present I have been able to total the distance travelled since the last serviced. What I would like to be able to do is display how much time - (in months, weeks and days) till the next service interval, knowing that each service interval is three months or six months hence. In other words count down the time from previous service to next service. Can someone, firstly tell me if it can be done and secondly how can it be done ?? -- Steven. In God we trust, all others we virus scan. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Can this be done
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
