Working days

wildturkey

Board Regular
Joined
Feb 21, 2006
Messages
174
Office Version
  1. 365
Platform
  1. Windows
Have tried, have failed...

Cell F1 has todays date in it, Cells E1 to B1 should have the preceeding four days but if its a weekend the days are skipped. Ive then formatted to show the day only eg

Tue Wed Thur Fri Mon
Wed Thur Fri Mon Tue
Thur Fri Mon Tue Wed etc etc

Currently have the worlds longest nested IF that still doesnt work :(
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,261
Office Version
  1. 365
Platform
  1. MacOS
have you tried workday()
so
WORKDAY(F1,-4)
i then copied that from E1 to D1 etc , so each cell has 4 days less than next cell and ignores weekends
you can also ad dholidays , so it will ignore those as well

Cell Formulas
RangeFormula
B1:E1B1=WORKDAY(C1,-4)
F1F1=TODAY()
 

Kerryx

Board Regular
Joined
May 6, 2016
Messages
102
or to show the day =TEXT(WORKDAY(F1,-4),"dddd")
-4 = no.of day from date in this instance 4 dates prior to the date etc.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,261
Office Version
  1. 365
Platform
  1. MacOS
Missed that
OR just format the cell as DDDD
it still has the date , if you need to work later on the actual date but displays the day

Cell Formulas
RangeFormula
B1:E1B1=WORKDAY(C1,-4)
F1F1=TODAY()
 

Kerryx

Board Regular
Joined
May 6, 2016
Messages
102

ADVERTISEMENT

or as etaf said formatting the cells works as well
:ROFLMAO: sometimes miss the east things
 

wildturkey

Board Regular
Joined
Feb 21, 2006
Messages
174
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I was missing Workday - had tried weekday and that's where I was having issues - many thanks all
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,261
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,399
Messages
5,769,849
Members
425,574
Latest member
grimeslisa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top