| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Formula Help!!
I have a daily report that I am in the process of automating. The
report needs to date sales from the day before, excluding the weekend. E.g. today is Friday 28th, the report is run for Thurs 27th. I can do this using the =TODAY()-1 function but this comes unstuck on Mondays, as the report then lists Sundays date and I need it to list Fridays instead. Does anyone know of a way to do this?? Cheers! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formula Help!!
You could use WEEKDAY as an input to an IF function, or use the WORKDAY
function. You'll find the relevant functions in Excel help. -- David Biddulph wrote in message oups.com... I have a daily report that I am in the process of automating. The report needs to date sales from the day before, excluding the weekend. E.g. today is Friday 28th, the report is run for Thurs 27th. I can do this using the =TODAY()-1 function but this comes unstuck on Mondays, as the report then lists Sundays date and I need it to list Fridays instead. Does anyone know of a way to do this?? Cheers! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formula Help!!
Try:
=IF(WEEKDAY(TODAY(),2)=1,TODAY()-3,TODAY()-1) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message oups.com... I have a daily report that I am in the process of automating. The report needs to date sales from the day before, excluding the weekend. E.g. today is Friday 28th, the report is run for Thurs 27th. I can do this using the =TODAY()-1 function but this comes unstuck on Mondays, as the report then lists Sundays date and I need it to list Fridays instead. Does anyone know of a way to do this?? Cheers! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formula Help!!
Thanks for the quick response, that formula is bringing up yesterdays
date but when I change my PC clock to a Monday it still brings up yesterdays date (as in the 27th not Sunday 30th). Can you explain the formula step by step so I can understand what it's trying to achieve? Thanks again for your help, it's much appreciated! Max wrote: Try: =IF(WEEKDAY(TODAY(),2)=1,TODAY()-3,TODAY()-1) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message oups.com... I have a daily report that I am in the process of automating. The report needs to date sales from the day before, excluding the weekend. E.g. today is Friday 28th, the report is run for Thurs 27th. I can do this using the =TODAY()-1 function but this comes unstuck on Mondays, as the report then lists Sundays date and I need it to list Fridays instead. Does anyone know of a way to do this?? Cheers! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formula Help!!
The functions which Max used are standard Excel functions. IF, WEEKDAY, and
TODAY are all listed in Excel help, so that you can look at it step by step. Are you sure that Tools/ Options/ Calculation is set to Automatic, not Manual? Have you updated your spreadsheet since you changed your PC clock? Did you try saving it? What does the function =TODAY() show you, when your PC clock is set to Monday's date and Max's formula supposedly gives yesterday's Thursday date? -- David Biddulph wrote in message ups.com... Thanks for the quick response, that formula is bringing up yesterdays date but when I change my PC clock to a Monday it still brings up yesterdays date (as in the 27th not Sunday 30th). Can you explain the formula step by step so I can understand what it's trying to achieve? Thanks again for your help, it's much appreciated! Max wrote: Try: =IF(WEEKDAY(TODAY(),2)=1,TODAY()-3,TODAY()-1) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message oups.com... I have a daily report that I am in the process of automating. The report needs to date sales from the day before, excluding the weekend. E.g. today is Friday 28th, the report is run for Thurs 27th. I can do this using the =TODAY()-1 function but this comes unstuck on Mondays, as the report then lists Sundays date and I need it to list Fridays instead. Does anyone know of a way to do this?? Cheers! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formula Help!!
|
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date Formula Help!!
Cheers David,
It was set to auto update but for some reason it doesn't. When I press F9 it brings up the correct date. Thanks for all your help gents! David Biddulph wrote: The functions which Max used are standard Excel functions. IF, WEEKDAY, and TODAY are all listed in Excel help, so that you can look at it step by step. Are you sure that Tools/ Options/ Calculation is set to Automatic, not Manual? Have you updated your spreadsheet since you changed your PC clock? Did you try saving it? What does the function =TODAY() show you, when your PC clock is set to Monday's date and Max's formula supposedly gives yesterday's Thursday date? -- David Biddulph wrote in message ups.com... Thanks for the quick response, that formula is bringing up yesterdays date but when I change my PC clock to a Monday it still brings up yesterdays date (as in the 27th not Sunday 30th). Can you explain the formula step by step so I can understand what it's trying to achieve? Thanks again for your help, it's much appreciated! Max wrote: Try: =IF(WEEKDAY(TODAY(),2)=1,TODAY()-3,TODAY()-1) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message oups.com... I have a daily report that I am in the process of automating. The report needs to date sales from the day before, excluding the weekend. E.g. today is Friday 28th, the report is run for Thurs 27th. I can do this using the =TODAY()-1 function but this comes unstuck on Mondays, as the report then lists Sundays date and I need it to list Fridays instead. Does anyone know of a way to do this?? Cheers! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date Formula Problem - Leave date blank if Null | Excel Worksheet Functions | |||
| Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
| Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
| Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
| Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
Linear Mode
